CASE Statement for Order By Clause with Multiple Columns and Desc/Asc Sort

Question

Following on from my earlier question here Case statement for Order By clause with Desc/Asc sort I have a statement like this:

SELECT      * FROM     TableName WHERE ORDER BY      CASE @OrderByColumn WHEN 1 THEN Forename END DESC,      CASE @OrderByColumn WHEN 2 THEN Surname END ASC  

This works well, but sometimes I need more than column in the order by. I actually need something like this:

..... ORDER BY     CASE @OrderByColumn WHEN 1 THEN Forename, Date, Location END DESC 

I can't work out how to make the CASE statement allow multiple columns in the THEN part.

Answer 1

You can write multiple cases, even if they all have the same condition.

ORDER BY      CASE @OrderByColumn WHEN 1 THEN Forename END DESC,      CASE @OrderByColumn WHEN 1 THEN Date END,      CASE @OrderByColumn WHEN 1 THEN Location END,      CASE @OrderByColumn WHEN 2 THEN Surname END ASC  

Actually, you don't specify a column to sort by, but an expression.

The case statement returns null if the condition is not met, so actually it means:

CASE @OrderByColumn WHEN 1 THEN Forename ELSE NULL END 

So if @OrderByColumn is not 1 then the statement returns always NULL. That doesn't exclude it from sorting, by the way, but it puts all those rows together in the result, making 'SurName' the decisive sorting within that group of rows.