Hi Everyone,
I've been battling this for two days with no luck. I'm using SQL
Server 2000.
Here's the mystery: I've got a stored procedure that takes a single
varchar parameter to determine how the result set is sorted. Here it
is:
CREATE PROCEDURE spDemo @SortField varchar(30)
AS
SELECT dtmTimeStamp, strEmpName, strOld, strNew, strActionDesc
FROM ActivityLog
ORDER BY CASE @SortField
WHEN 'dtmTimeStamp' THEN dtmTimeStamp
WHEN 'strEmpName' THEN strEmpName
WHEN 'strOld' THEN strOld
WHEN 'strNew' THEN strNew
WHEN 'strActionDesc' THEN strActionDesc
END
GO
When I execute the stored procedure in the Query Analyzer, it works
perfectly ONLY IF the @SortField parameter is 'dtmTimeStamp' or
'strNew'. When passing in any of the other three possible values for
@SortField, I get the following error:
Server: Msg 241, Level 16, State 1, Procedure spDemo, Line 4
Syntax error converting datetime from character string.
Now instead of executing the stored procedure, if I copy and paste the
SELECT statement directly into the Query Analyzer (after removing the
CASE statement and manually trying each different value of @SortField),
it works fine for all five possible values of SortField.
Even though the error points to Line 4 of the stored procedure, it
seems to me that the CASE statement is causing problems for some, but
not all, values of the @SortField parameter.
Any ideas?
Thanks,
Jimmy 4 9536
Check the data type for each of the column. when using case statement
in the order by clause it should be the same. If i am not wrong
"strNew" column is also a datetime field. That's why you are not
getting any error for strNew. Since the first case statement is trying
to sort "dtmTimeStamp" which is datetime datatype, when you try to pass
the parameter as "strEmpName" it is trying to convert the strEmpName to
string. I would suggest adding a convert function to all datatypes
which are not character datat type and convert it to a string. for date
you should use covnert(varchar(50),dtmTimeStamp, 21) if you want to
sort upto the mlli second. use the same way for integer. This is a good
option if you are sorting only smaller set of rows. If you are sorting
a larger set trying having separate sql's for each order. i hope this
helps.
Thanks
Ramesh
On 6 Jan 2005 08:51:35 -0800, sh****@gmail.com wrote: Hi Everyone,
I've been battling this for two days with no luck. I'm using SQL Server 2000.
Here's the mystery: I've got a stored procedure that takes a single varchar parameter to determine how the result set is sorted. Here it is:
CREATE PROCEDURE spDemo @SortField varchar(30)
AS SELECT dtmTimeStamp, strEmpName, strOld, strNew, strActionDesc FROM ActivityLog ORDER BY CASE @SortField WHEN 'dtmTimeStamp' THEN dtmTimeStamp WHEN 'strEmpName' THEN strEmpName WHEN 'strOld' THEN strOld WHEN 'strNew' THEN strNew WHEN 'strActionDesc' THEN strActionDesc END GO
(snip)
Hi Jimmy,
The explanation Ramesh gives you is correct. However, instead of manually
converting all data to the same datatype (which is often clumsy and can
even make proper sorting impossible), you should use several CASE
statement:
ORDER BY CASE WHEN @SortField = 'TimeStamp' THEN TimeStamp END,
CASE WHEN @SortField = 'EmpName' THEN EmpName END,
....
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Hugo, I just fell in love with you. It worked.
Thanks,
Jimmy
On 6 Jan 2005 13:36:36 -0800, sh****@gmail.com wrote: Hugo, I just fell in love with you. It worked.
LOL!
I'm sorry Jimmy - I'm already married <g>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: LineVoltageHalogen |
last post by:
Greetings All, I was hoping that someone out there has run into this
issue before and can shed some light on it for me. I have a stored
procedure that essentially does a mini ETL from a source...
|
by: Jim in Arizona |
last post by:
I'm going insane! I don't know if it's just that the .net 2.0 framework is
buggy or if it really is my code.
This is pretty hard to explain since I can't even begin to nail down why
this is...
|
by: Cor Ligthert |
last post by:
Hello everybody,
Jay and Herfried are telling me every time when I use CDate that using the
datetime.parseexact is always the best way to do String to datetime
conversions. They don't tell why...
|
by: |
last post by:
Im getting that error (It is strange. I Run my programme step by step
pressing f11. im looping SqlCommand in a while loop. The First step runs but
when a enter 2nd step in my loop it returns error...
|
by: =?Utf-8?B?QmFidU1hbg==?= |
last post by:
Hi,
I have a GridView and a SqlDataSource controls on a page. The SqlDataSource
object uses stored procedures to do the CRUD operations. The DataSource has
three columns one of which -...
|
by: Brad Pears |
last post by:
I am using vb.net 2005 and SQL server 2000. In my table I have a date field
of type "smalldatetime". In my vb application, the user may or may not enter
a date value into the appropriate text box....
|
by: Vish4u |
last post by:
Hello Everyone,
I have a encountered a strange issue with the execution of my stored procedure on clients machine.
My stored procedure contains a cursor in which there is a select statement...
|
by: Sreenivas |
last post by:
Hi every one,
I need to compare to datetime values , done in
stored procedure.The input datetime parameter is in
dd/mm/yyyy hh:mm:ss AM/PM format ,the datetime values to be compared
are also...
|
by: sirdavethebrave |
last post by:
Hi guys -
I have written a form, and a stored procedure to update the said form.
It really is as simple as that. A user can go into the form, update some fields and hit the update button to...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |