Wednesday 15 July 2009

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

No comments: