Here is a way to change the Order By in a sproc using Case. I find it useful when I am writing a sproc that specifically feeds a report (usually in SSRS) and has different sort possibilities. After the user picks the report and how they want it sorted from the application it is outputted directly to a PDF so they can’t change the sorting. Usually I will use a DropDownList with the various sorting criteria and pass the value back to the sproc (the @OrderBy param in the examples).
For a regular SELECT statement:
@OrderBy int
SELECT * FROM tblWhatever
ORDER BY CASE WHEN @OrderBy = 0 THEN xyz
WHEN @OrderBy = 1 THEN abc
ELSE qwerty
END
For a SELECT statement with a UNION
@OrderBy int
SELECT * FROM
(SELECT abc, xyz, qwerty FROM tblWho
UNION
SELECT abc, xyz, qwerty FROM tblWhere) lmno
ORDER BY CASE WHEN @OrderBy = 0 THEN xyz
WHEN @OrderBy = 1 THEN abc
ELSE qwerty
END