Strange datetime conversion error in stored procedure.

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

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, shyner@gmail.com wrote:
[color=blue]
>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[/color]
(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, shyner@gmail.com wrote:
[color=blue]
>Hugo, I just fell in love with you. It worked.[/color]

LOL!

I'm sorry Jimmy - I'm already married <g>

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)