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
WHEN @SortOrder = 1 THEN CompanyName
WHEN @SortOrder = 2 THEN ContactName
To get around the datatype problem, use seperate group CASE statements for each datatype
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.