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:
declare @col varchar(100)
set @col = 'CustomerID'
select *
from sales.salesorderHeader
order by case when @col = 'CustomerID' then customerID
else salesOrderID end
Now here is the beauty - I can make it work against aggrigated columns, too:
declare @col varchar(100)
set @col = 'unitprice'
select salesorderid,
sum(unitprice) as unitprice,
sum(linetotal) as linetotal
from sales.salesorderdetail
group by salesorderid
order by case when @col = 'unitprice' then sum(unitprice)
else salesOrderID end