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

http://www.sqlteam.com/article/dynamic-order-by

sql order by with case

sql, order

SQL