ASC and DESC in dynamic ORDER BY clause

Question

Post reply

ASC and DESC in dynamic ORDER BY clause

Magy

SSCarpal Tunnel

Points: 4217
More actions
February 12, 2010 at 1:23 pm

#229947

I have a block of code in my stored procedure that allows for dynamic ORDER BY. However, I cannot use ASC or DESC as it causes an error. Is there a way to include/use ASC and DESC in code like this?
Thanks!
ORDER BY CASE WHEN @SortOrder = 1 THEN eName
WHEN @SortOrder = 2 THEN eDisplay
WHEN @SortOrder = 3 THEN pName
WHEN @SortOrder = 4 THEN sLastName
WHEN @SortOrder = 5 THEN D.Type
WHEN @SortOrder = 6 THEN CAST(E.fDateStart AS VARCHAR(50))
ELSE elementName
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I couldn't find an elegant way but here's what might work if you have to have a paramterized option for ASC/DESC:

DECLARE @SortOrder INT

SET @SortOrder=2

DECLARE @SortOrder2 INT

SET @SortOrder2=2

;WITH cte AS

(

SELECT

ROW_NUMBER() OVER(ORDER BY

ORDER BY

CASE WHEN @SortOrder = 1 THEN eName

WHEN @SortOrder = 2 THEN eDisplay

WHEN @SortOrder = 3 THEN pName

WHEN @SortOrder = 4 THEN sLastName

WHEN @SortOrder = 5 THEN D.Type

WHEN @SortOrder = 6 THEN CAST(E.fDateStart AS VARCHAR(50))

ELSE elementName

END

) ROW,

YourCols

FROM YourTable

)

SELECT YourCols

FROM cte

ORDER BY

CASE WHEN @SortOrder2=1 THEN ROW ELSE -ROW END

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 2

I'm not sure if this would work...does this use ASC or DESC?

Thanks!

LutzM SSC Guru Points: 107049 More actions · Answer 3

Magy (2/15/2010)
I'm not sure if this would work...does this use ASC or DESC?
Thanks!

Yes and no.

No, since you won't find the keywords.

But yes, since this code's using a little trick:

I used the ROW_NUMBER() function to get the data sorted according to the order specified by @SortOrder. You could test the result by running the query that's inside the CTE.

The query outside the CTE will order the results of ROW_NUMBER() in ascending order when @SortOrder2=1, otherwise it will order by the negative value of row, which is equal to a descending order.

The easiest way to check the results is to set up some sample data and give it a try!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ASC and DESC in dynamic ORDER BY clause

Cookies on SQLServerCentral