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.
This Post Has 5 Comments
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…
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).
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
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.
Thanks for this! Saved me a lot of time and headaches!