You heard it here first, folks.
I searched the web for this solution, but could not find anything that worked. After a bit of hacking, I came up with a way to put variables in an ORDER BY clause, and even have it work for GROUP BY queries. Read on.
This statement (written against AdventureWorks) will generate the following error:
declare @col varchar(100)
set @col = 'CustomerID'
select *
from sales.salesorderHeader
order by @col
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.
Fix it like this: