How do I use a variable in an ORDER BY clause?

Often, we want to allow users to determine how their data will be ordered. So, let's say we have a table like this: 
 

CREATE TABLE blat  (      blatID INT,      hits INT,      firstname VARCHAR(3),      email VARCHAR(9)  )  GO    SET NOCOUNT ON  INSERT blat VALUES(1, 12, 'bob', 'bob@x.com')  INSERT blat VALUES(2, 8,  'sue', 'sue@x.com')  INSERT blat VALUES(3, 17, 'pat', 'pat@x.com')  INSERT blat VALUES(4, 4,  'pam', 'pam@x.com')  INSERT blat VALUES(5, 1,  'jen', 'jen@x.com')  INSERT blat VALUES(6, 3,  'rod', 'rod@x.com')  INSERT blat VALUES(7, 5,  'nat', 'nat@x.com')  INSERT blat VALUES(8, 19, 'rob', 'rob@x.com')  INSERT blat VALUES(9, 24, 'jan', 'jan@x.com')  INSERT blat VALUES(10, 0, 'meg', 'meg@x.com')  GO

 
The first thing people tend to try in T-SQL is the following: 
 

DECLARE @col VARCHAR(9)  SET @col = 'firstname'  SELECT * FROM blat ORDER BY @col  GO

 
However, this returns: 
 

Server: Msg 1008, Level 15, State 1, Line 4  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.

 
Clearly, SQL Server doesn't like variables in the ORDER BY clause. Well, we can just use dynamic SQL, right? 
 

DECLARE @col VARCHAR(9)  SET @col = 'firstname'  EXEC('SELECT * FROM blat ORDER BY '+@col)  GO

 
But dynamic SQL is not a very desirable solution. (See Erland's text for some of the problems.) 
 
And then we could always construct an ad hoc query to send to the database from ASP. But we all know what a bad idea that is, right? 
 
So, are there other ways to skin this cat? Of course. Some people try this: 
 

DECLARE @col VARCHAR(9)  SET @col = 'firstname'    SELECT * FROM blat ORDER BY       IF @col = 'firstname' THEN firstname      IF @col = 'email' THEN email  GO

 
But this returns: 
 

Server: Msg 156, Level 15, State 1, Line 10  Incorrect syntax near the keyword 'IF'.  Server: Msg 156, Level 15, State 1, Line 10  Incorrect syntax near the keyword 'THEN'.  Server: Msg 156, Level 15, State 1, Line 11  Incorrect syntax near the keyword 'THEN'.

 
This is because you can't put an IF / THEN statement within a query expression—IF is used for program flow. And yes, you can construct an example like this: 
 

DECLARE @col VARCHAR(9)  SET @col = 'firstname'    IF @col = 'firstname'      SELECT * FROM blat ORDER BY firstname    IF @col = 'email'      SELECT * FROM blat ORDER BY email  GO

 
However, this isn't going to be very fun if the number of columns is quite large, and if the SELECT query is complex, you probably don't want to repeat it for every possible column. And we haven't even introduced making the order direction (ASC/DESC) a parameter yet. :-) 
 
Another workaround is to use a CASE expression in the ORDER BY clause. There are a couple of "rules" that you must follow. The most important is that all possible results of a CASE expression must be of the same datatype (or must have the correct data precedence to implicitly convert). So let's try a small example, where only email or firstname are allowed. 
 

DECLARE @col VARCHAR(9)  SET @col = 'firstname'    IF @col IN ('firstname', 'email')      SELECT * FROM blat          ORDER BY CASE @col              WHEN 'firstname' THEN firstname              WHEN 'email' THEN email          END  ELSE      SELECT * FROM blat  GO

 
A rule you can take advantage of, in order to allow columns of different datatypes to be ordered conditionally, is that an ORDER BY step containing NULL is ignored. So, you can do this to separate conditionals based on each data type, without worrying about order taking precedence: 
 

DECLARE @col VARCHAR(9)  SET @col = 'hits'        SELECT * FROM blat          ORDER BY          CASE @col              WHEN 'firstname' THEN firstname              WHEN 'email' THEN email          END,          CASE @col              WHEN 'blatID' THEN blatID              WHEN 'hits' THEN hits          END  GO

 
Now, what if you want to pass the ascending/descending order a variable as well? It gets a little more complicated, since the parser for T-SQL is very fussy about where that DESC keyword goes. Here is the example I came up with: 
 

DECLARE @col VARCHAR(9), @dir VARCHAR(4)  SET @col = 'hits'  SET @dir = 'desc'        SELECT * FROM blat          ORDER BY          CASE @dir              WHEN 'desc' THEN               CASE @col                  WHEN 'firstname' THEN firstname                  WHEN 'email' THEN email                  END              END              DESC,          CASE @dir              WHEN 'desc' THEN                           CASE @col                  WHEN 'blatID' THEN blatID                  WHEN 'hits' THEN hits                  END              END              DESC,          CASE @dir              WHEN 'asc' THEN               CASE @col                  WHEN 'firstname' THEN firstname                  WHEN 'email' THEN email                  END              END,          CASE @dir              WHEN 'asc' THEN               CASE @col                  WHEN 'blatID' THEN blatID                  WHEN 'hits' THEN hits                  END              END  GO

 
Now there is a little trick you can use for numeric values, to switch between ASC and DESC, with a little less logic than above. You can invert a column to negative so that it sorts the opposite way in the default order (ASC). Then you don't have to worry about the finnicky use of the ASC/DESC keywords. I'll use a simpler example to demonstrate its use: 
 

DECLARE @col VARCHAR(9), @dir VARCHAR(4), @mult INT  SET @col = 'hits'  SET @dir = 'desc'    SET @mult = CASE @dir      WHEN 'desc' THEN -1      ELSE 1      END          SELECT * FROM blat          ORDER BY          CASE @col              WHEN 'blatID' THEN @mult * blatID              WHEN 'hits' THEN @mult * hits          END  GO

 
I don't suggest you try this for a varchar column; you should get the following error: 
 

Server: Msg 403, Level 16, State 1, Line 2  Invalid operator for data type. Operator equals minus, type equals varchar.

 
Now of course, clean up: 
 

DROP TABLE blat  GO

 
Please let us know if you have any further problems using these examples, or if there are examples we might be missing.