I was just asked a question about a way of making the order by clause in a query alter according to a variable without having to build up the sql query as a string and EXEC it.

After a bit of a play we got it to work using similar code to the following

DECLARE @intOrder INT
SET @intOrder = 4

SELECT *
FROM Customers
ORDER BY
CASE @intOrder
 WHEN 1 THEN CompanyName
 WHEN 2 THEN ContactName
 WHEN 3 THEN ContactTitle
 WHEN 4 THEN Fax
END
where CompanyName, ContactName, ContactTitle and Fax are all character columns.

There is a condition. The columns that are being potentially ordered by need to be of the same type, which means potentially casting columns. And of course, remembering that an integer may be of a different length to others and so needs to be padded to ensure that the comparison is appropriate.

DECLARE @intOrder INT
SET @intOrder = 3

SELECT *
FROM Employees
ORDER BY
CASE @intOrder
 WHEN 1 THEN LastName
 WHEN 2 THEN CONVERT(VARCHAR(20),BirthDate,121)
 WHEN 3 THEN REPLACE(STR(ReportsTo, 10), SPACE(1), '0')
 WHEN 4 THEN Title
END
where LastName and Title are character columns, BirthDate is a date - using format 121 to move it into ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h) and ReportsTo is an integer