I am trying to run a SELECT query from within a Stored Procedure and I need to change how I sort the SELECT depending on a variable that is passed into the SP. Of the three fields that the SELECT can be sorted by, one is datetime, one is int, and one is nvarchar(255). I have looked at several examples but none of them ever seem to fully work for me. All the examples used the CASE statement.
I have tried grouping all three WHENs under one CASE statement. This compiles OK but when I actually run the SP it works OK for the int field and the datetime field but I get a data type conversion error on the nvarchar field.
Ive tried using three seperate CASE statements, one for each field. I get a syntax error when I try to compile.
Ive tried nesting three CASE statements and again this compiles and runs OK on the int and datetime fields but gets a data type conversion error when sorted on the nvarchar field.
Also, I need to be able to specifiy how the sort is done, ASC v. DESC. If I try to put ASC or DESC on the same line as the WHEN and field name I get a syntax error when I try to compile. If I move the ASC or DESC after the END keyword, which has been suggested on a couple of examples Ive seen, I get syntax errors on the CASE statement when I try to compile.
Here's what is the most successful so far:
SELECT * FROM #TemporaryTable WHERE (RowID >= @Start) AND (RowID <= @End)
WHEN 1 THEN empLastName
WHEN 2 THEN chkLockedFor
WHEN 3 THEN chkLockedDate
This compiles and runs OK for @Sort = 2 & 3 but gives this error when @Sort = 1:
Syntax error converting datetime from character string.
Any ideas would be appreciated.