Dynamic ordering with T-SQL

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
SELECT *
FROM Table1
ORDER BY
   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:

SELECT *
FROM Table1
ORDER BY
   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.

5 thoughts on “Dynamic ordering with T-SQL”

  1. 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. 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. 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
    ORDER BY
    CASE WHEN @SortColumn = ‘custname’ THEN custname END DESC

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

  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *