T-SQL Conditional Ordering: CASE ORDER BY

You can conditionally order a query depending on a variable by using the case statement in the order by.

DECLARE @sort SMALLINT
SET @sort = 1

SELECT * FROM   Person.Contact
ORDER BY
CASE WHEN @sort=1 THEN FirstName ELSE LastName END