asked on

Conditional SORT BY

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)
ORDER BY
      CASE @Sort
            WHEN 1 THEN empLastName
            WHEN 2 THEN chkLockedFor
            WHEN 3 THEN chkLockedDate
      END

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adathelad

Hi Wilbat,

All 3 fields that may be ordered by must be of the same datatype, so you should convert the chkLockedFor and chkLockedDate fields to varchars:

SELECT * FROM #TemporaryTable WHERE (RowID >= @Start) AND (RowID <= @End)
ORDER BY
CASE @Sort
WHEN 1 THEN empLastName
WHEN 2 THEN cast(chkLockedFor as varchar)
WHEN 3 THEN cast(chkLockedDate as varchar)
END

Wilbat

ASKER

angelIII
that works well, but is there a way to specify ASC or DESC?

adathelad
that wont work because it kills the ability to sort correctly on the datetime field, thanks though.

Wilbat

ASKER

nevermind angelIII, I figured it out.
kudos, you're the big winner!

Wilbat

ASKER

for anyone that may want to know how I did the sorting, here it is:

SELECT * FROM #TemporaryTable WHERE (RowID >= @Start) AND (RowID <= @End)
ORDER BY
      CASE WHEN @Sort = 1 THEN empLastName ELSE NULL END DESC,
      CASE WHEN @Sort = 2 THEN chkLockedFor ELSE NULL END DESC,
      CASE WHEN @Sort = 3 THEN chkLockedDate ELSE NULL END ASC

just put the DESC or ASC after END.