Dynamic ORDER BY

Print Page
Dynamic ORDER BY
let's see how CASE can be used to implement a dynamic ORDER BY. Assume you want to sort the results of the Customers query on any of the columns referenced in the SELECT. The following statements show how this is done.

DECLARE @SortOrder tinyint
SET @SortOrder = 2
SELECT CompanyName, ContactName,ContactTitle
FROM Customers
ORDER BY
CASE
WHEN @SortOrder = 1 THEN CompanyName
WHEN @SortOrder = 2 THEN ContactName
ELSE ContactTitle
END

To get around the datatype problem, use seperate group CASE statements for each datatype

ORDER BY
CASE WHEN @SORTBY = 1 THEN Numeric1
WHEN @SORTBY = 2 THEN Numeric2 END,
CASE WHEN @SORTBY = 3 THEN VarChar1
WHEN @SORTBY = 4 THEN VarChar2 END DESC,
CASE WHEN @SORTBY = 5 THEN DateTime1END

Note that each case block is seperated by a comma (after END) except the last block. Also note that you can use ASC or DESC for each CASE statement group.