CASE statement for multi-column order with descriptor / Asc Sort

Question

Following my previous question here, the Case statement for an Order By clause with Desc / Asc sorting . I have an instruction like this:

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

This works well, but sometimes I need more than a column in order. I really need something like this:

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

I cannot figure out how to make the CASE statement allow multiple columns in the THEN part.

Answer 1

You need it?

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

Answer 2

You can write several 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 DESC, CASE @OrderByColumn WHEN 1 THEN Location END DESC, CASE @OrderByColumn WHEN 2 THEN Surname END ASC 

In fact, you are not specifying a column to sort, but an expression.

The case statement returns zero if the condition is not met, so this actually means:

 CASE @OrderByColumn WHEN 1 THEN Forename ELSE NULL END 

So if @OrderByColumn is not 1, then the operator always returns NULL. By the way, this does not exclude it from sorting, but combines all of these lines as a result, making SurName decisive sorting in this group of lines.