SQL orderby / case issue: orderdirection fail

Posted by Joris on Stack Overflow See other posts from Stack Overflow or by Joris
Published on 2010-04-25T11:47:52Z Indexed on 2010/04/25 11:53 UTC
Read the original article Hit count: 449

Filed under:
|
|

I got a stored procedure that delivers a table of students, and it needs to order by surname, name etc... it also needs to sort ascending, descending, depending on the parameter @orderby...

code:

ORDER BY
CASE 
 WHEN @orderby = 'studentkey' THEN Studentkey END ASC,
CASE 
 WHEN @orderby = 'studentkey' and @desc = 1 THEN Studentkey END DESC,
CASE
 WHEN @orderby = 'initials' THEN Initials END ASC,
CASE
 WHEN @orderby = 'initials' and @desc = 1 THEN Initials END DESC,
CASE
 WHEN @orderby = 'firstname' THEN Firstname END ASC,
CASE
 WHEN @orderby = 'firstname' and @desc = 1 THEN Firstname END DESC,
CASE
 WHEN @orderby = 'nickname' THEN Nickname END ASC,
CASE
 WHEN @orderby = 'nickname' and @desc = 1 THEN Nickname END DESC, 
CASE
 WHEN @orderby = 'insertion' THEN Insertion END ASC,
CASE
 WHEN @orderby = 'insertion' and @desc = 1 THEN Insertion END DESC,
CASE
 WHEN @orderby = 'surname' THEN Surname END ASC,
CASE
 WHEN @orderby = 'surname' and @desc = 1 THEN Surname END DESC
NED

There is a difference in output between @desc = 1 and @desc = 0, but not what i desire...

Does anyone have a solution?

© Stack Overflow or respective owner

Related posts about sql-server-2008

Related posts about orderby