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

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.

Do you need this?

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