Dynamic ordering with T-SQL

May 12, 2008

This is an oldie, but a goodie. I was talking to Dave Gardiner about a question he had recently, the question of how to dynamically sort the results of a T-SQL query.

He was thinking of something like this:

–Not so useful – only works in a stored procedure
IF (@order = ‘Column1’)
    SELECT * from Table1 ORDER BY Column1
ELSE IF (@order = ‘Column2’)
    SELECT * from Table1 ORDER BY Column2

And one of the suggestions in his comments was to use the CASE statement:

–Not bad, but only works if the types are compatible
FROM Table1
   CASE WHEN @order = ‘Column1’ THEN Column1 ELSE Column2 END

This isn’t bad, but if Column1 and Column2 are different types (well, incompatible types), you get an error when the ELSE clause is used.

A better solution is to remember that CASE gives NULL if there nothing is satisfied:

FROM Table1
   CASE WHEN @order = ‘Column1’ THEN Column1 END,
   CASE WHEN @order = ‘Column2’ THEN Column2 END

This way, it’s either ordered by “Column1, NULL” or by “NULL, Column2”. Both times, it’s the order we want, and we’re doing it in a single query.

This Post Has 5 Comments

  1. Daniel Godoy

    Isnt there a risk of generating system comands with the leveraging of null values… What if 2 nulls occur in a row… Can that be inturpreted as an end of test by the system? not too sure what happens under the hood of SQL2k5 but from a programming perspective… just a thought…

  2. Rob Farley

    Daniel – can you explain what you mean here? If neither condition is satisfied and you sort by NULL, NULL, then that’s perfectly legal. It just won’t produce the data in a known order (it’ll use whichever order it access the data).

  3. REJI P R

    This query cause a problem when we want to sort in DESC Order ….For that purpose Query must be like
    SELECT custid, custname
    FROM Customers
    CASE WHEN @SortColumn = ‘custname’ THEN custname END DESC

    CASE WHEN @SortColumn = ‘custid’ THEN custid END DESC

  4. Rob Farley

    Yes Reji, I should’ve mentioned that if you want Descending, you need to put that outside the CASE statement, and if you want to pass that in as an option, then you’ll need an extra CASE for each one.

  5. Tony

    Thanks for this! Saved me a lot of time and headaches!

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.


Related Blogs