Dynamic Sort Order In T-SQL

create proc GetEmployees
@SortField nvarchar(20)
as

select * from Employees
order by
case @SortField
when 'FirstName' then cast (FirstName as sql_variant)
when 'LastName' then cast (LastName as sql_variant)
when 'HireDate' then cast (HireDate as sql_variant)
else cast (EmployeeID as sql_variant)
end

Very clever. Originally found at http://www.angrycoder.com/article.aspx?ArticleID=131 "Dynamic Sort Order In T-SQL"

A comment states:
There are two problems (at least) with the code. First, it will not use indexes and thus be fairly slow. And second, it will sort numerical columns alphabeticaly. That is 1, 11, 2, 3, 35, 4, ... Usually not exactly what you wanted.While you might solve the second problem by prepending enough zeroes or spaces or something it would only make the first problem worse. It's SLOW!Sorry, there is no nice solution. There can't really be as the order column can totally change the optimal execution plan. And you can't (so far) have several execution plans for a single query. So ... it's anoying, but the only performance effective solution is to repeat the query as many times as you have the different orders and choose the right one by a row of IF statements :-(