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.