TSQL: ORDER BY - either use a column or nothing (empty)

Question

Currently I have the following query in TSQL:

select *
from table
order by
    col1,
    col2

But there's a condition (@cond = 1), which if it is true, then I should rather sort by column col0 DESC and then use the rest:

select *
from table
order by
    case when @cond = 1 then col0 end DESC,
    col1,
    col2

which works fine when the condition is true. But if it's false, then I have an error message (in that case I want to have the initial query).

How to make the trick here?

Answer 1

Because you have a difference in sort order (ASC/DESC) and the number of columns (1 vs 2) we will need to sort on 3 CASE statements.

SQL-Server example:

DECLARE @cond bit;
SET @cond = 0;

SELECT *
FROM [table]
ORDER BY 
    CASE WHEN @cond = 1 THEN col0  END DESC, 
    CASE WHEN @cond = 0 then  col1 END,
    CASE WHEN @cond = 0 then  col2 END

SET @cond = 1;

SELECT *
FROM [table]
ORDER BY 
    CASE WHEN @cond = 1 THEN col0  END DESC, 
    CASE WHEN @cond = 0 then  col1 END,
    CASE WHEN @cond = 0 then  col2 END

fiddle

col0	col1	col2
1	2	3
4	5	6
7	8	9

col0	col1	col2
7	8	9
4	5	6
1	2	3