ASC and DESC in dynamic ORDER BY clause

  • 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

  • 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]

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

    Thanks!

  • 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]

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

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