Geeks With Blogs
Bunch's Blog One day I'll have a catchy subtitle, one day

Here is a way to change the Order By in a sproc using Case. I find it useful when I am writing a sproc that specifically feeds a report (usually in SSRS) and has different sort possibilities. After the user picks the report and how they want it sorted from the application it is outputted directly to a PDF so they can’t change the sorting. Usually I will use a DropDownList with the various sorting criteria and pass the value back to the sproc (the @OrderBy param in the examples).

For a regular SELECT statement:

@OrderBy int
SELECT * FROM tblWhatever
ORDER BY CASE WHEN @OrderBy = 0 THEN xyz
                         WHEN @OrderBy = 1 THEN abc
                         ELSE qwerty
                END

For a SELECT statement with a UNION

@OrderBy int
SELECT * FROM
(SELECT abc, xyz, qwerty FROM tblWho
UNION
SELECT abc, xyz, qwerty FROM tblWhere) lmno
ORDER BY CASE WHEN @OrderBy = 0 THEN xyz
                          WHEN @OrderBy = 1 THEN abc
                          ELSE qwerty
                 END

Technorati Tags:
Posted on Tuesday, April 7, 2009 2:13 PM T-SQL | Back to top


Comments on this post: Dynamic Order By in Stored Procedure

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Bunch | Powered by: GeeksWithBlogs.net