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.
WilbatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to split like this, to avoid data type conversion issues:
SELECT * FROM #TemporaryTable WHERE (RowID >= @Start) AND (RowID <= @End)
ORDER BY CASE WHEN @Sort = 1 THEN empLastName ELSE NULL END
,CASE WHEN @Sort = 2 THEN chkLockedFor ELSE NULL END
,CASE WHEN @Sort = 3 THEN chkLockedDate ELSE NULL END

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
adatheladCommented:
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



0
WilbatAuthor Commented:
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.
0
WilbatAuthor Commented:
nevermind angelIII, I figured it out.
kudos, you're the big winner!
0
WilbatAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.