SQL - Dynamic Order By clause

Have you ever wanted a stored procedure to order it's results dynamically? The ORDER BY clause is the place to start but normally relates to a series of columns that's hard-coded. If you want this clause to change depending on some input, you can adapt the following SQL. It contains a switch statement that compares the value of a parameter against pre-defined string column names. When a match is found, the respective clause is inserted. You can also specify the direction (ascending/descending) which doubles the amount of case options.

declare @orderBy nvarchar(max)
set @orderBy = 'columnName'
 
-- 0 Descending
-- 1 Acsending
declare @orderByDirection int
set @orderByDirection = 0
 
select *
from tableName
order by
    case when @orderBy = 'columnName' and @orderByDirection = 0
        then columnName end desc,
    case when @orderBy = 'columnName' and @orderByDirection = 1
        then columnName end