Судя по всему, в общем случае надо использовать ORDER BY и динамический sql.

Не совсем понял Glory и SergSuper.

Вы предлагаете писать так?
\ndeclare @ot int
set @ot = 1

select *
from (select 1 x, 2 y, '3' z) t
ORDER BY
CASE @ot WHEN 1 THEN 'x' else '' end,
CASE @ot WHEN 2 THEN 'y' else '' end,
CASE @ot WHEN 3 THEN 'z' else 0 end


У меня SQLServer пишет так:

Server: Msg 1008, Level 15, State 1, Line 7
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.