Using a CASE in an ORDER BY statement?  It's documented already.  Why even write about this? 

I am writing about it because CASE code in an ORDER BY statement can be very strange, maddening, and confusing.  It is not well behaved and the documentation doesn't even mention these things. Sometimes your code will run and other times it won't.  You may get error messages and you may not.  The error messages you do get will seem to have nothing to do with your code.  The documentation doesn't address these issues at all.

It's just plain weird.

The Help doesn't help much

Yes, there are two examples in the Help collection.  The examples work.  If that's all you want to do then you don't need to read this article any further.

But really now, when was the last time that you wanted to do ONLY what you saw in a Help document?  You want to develop your own code, right?

Examples, some will work, some will not

So set the Help aside for now.  We're going to create our own code that we can adapt however we want.

For this example, imagine an application that displays a table of Employees, their ID numbers, and their dates of birth.  When the user clicks on a column heading on the screen the application will pass two parameters to the T-SQL.  They are the column name and either "ASC" or "DESC".  On the basis of those two parameters we want to sort the table.

We'll do it with the mysterious CASE statement. So let's demystify it.

Create a table in a test database with this T-SQL:

CREATE TABLE [Employee](
 [EmpID] [int] NOT NULL,
 [EmpName] [varchar](50) NOT NULL,
 [EmpDOB] [datetime] NULL,
CONSTRAINT [PK_Employee]
PRIMARY KEY CLUSTERED
(
 [EmpID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO Employee
 VALUES (1, 'Chandler', '5/3/1953')
INSERT INTO Employee
 VALUES (2, 'Enos', '9/2/1992')
INSERT INTO Employee
 VALUES (3, 'Baker', '8/10/1946')
INSERT INTO Employee
 VALUES (4, 'Davis', '4/1/1980')
INSERT INTO Employee
 VALUES (5, 'Franklin', '7/4/1976')

 We'll start with something we know.  Then we'll make modifications to it.

Here's an example of an ORDER BY statement that will sort the employee table from youngest date of birth (highest DOB) to oldest (lowest DOB):

SELECT
    Employee.EmpID,
    Employee.EmpName,
    Employee.EmpDOB
FROM Employee 
ORDER BY EmpDOB DESC

Here is the output:

CASE Statement

Well, CASE should give us what we need, right?  But CASE doesn't act the way you might expect.  It turns out that the following code WILL NOT work.  Do you know why?

DECLARE @sortKey_A varchar(50)
    SET @sortKey_A = 'EmpDOB ASC'
DECLARE @sortKey_D varchar(50)
    SET @sortKey_D = 'EmpDOB DESC'
DECLARE @sortType bit
SET @sortType = 0
SELECT
    Employee.EmpID,
    Employee.EmpName,
    Employee.EmpDOB
FROM Employee
ORDER BY
    CASE
        WHEN (@sortType = 0)
            THEN @sortKey_A
        ELSE @sortKey_D
    END

SQL Server 2005 produces the following error message.

Msg 1008, Level 15, State 1, Line 19

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.

Worse, SQL Server 2008 doesn't even return an error message.  The results are returned but they aren't sorted.

Yikes!

Objects vs. text

The T-SQL above fails because it is still trying to work with text. We need to be working with expressions that represent objects such as a column name or a computed amount.  "EmpDOB DESC" is not an expression that represents an object.  "EmpDOB DESC" is a piece of text.  If we try to use it as an expression, as in the T-SQL above, it may simply be ignored or it may result in a syntax or execution error.

How to do it:  The column name does represent an object.  So it can be placed inside the CASE statements.  But the "ASC" and "DESC" are not objects.  They must remain outside the CASE statements.

The following code WILL work.

DECLARE @sCol varchar(50)
    SET @sCol = 'EmpDOB'
DECLARE @sOrder varchar(4)
    SET @sOrder = 'DESC'
DECLARE @sortKey varchar(55)
    SET @sortKey = @sCol + ':' + @sOrder
SELECT
    Employee.EmpID,
    Employee.EmpName,
    Employee.EmpDOB
FROM Employee
ORDER BY
    CASE
        WHEN (@sortKey = 'EmpDOB:ASC')
            THEN Employee.EmpDOB
    END ASC,
    CASE
        WHEN (@sortKey = 'EmpDOB:DESC')
            THEN Employee.EmpDOB
    END DESC

Wait a minute!  What about that "ASC," after the END of the first CASE statement, above?  If that CASE statement does not produce output, then wouldn't the four characters "ASC," simply be extra characters hanging out in the breeze?  Wouldn't this cause a syntax error?

Answer:  Yes they are extra characters.  No they do not cause a syntax error because somehow CASE forgives this.

Here are the parameters and the output:

 SET @sCol = 'EmpDOB'
 SET @sOrder = 'DESC'

 

Multiple sort keys, another weirdism

What about multiple sort keys?  Let's replace the CASE statement with one that should accommodate ascending or descending sorts.  It should also sort on either the EmpDOB column or the EmpName column.

Unfortunately, it happens that the following SOMETIMES WILL WORK AND SOMETIMES WILL NOT.  Why?

   CASE
        WHEN (@sortKey = 'EmpName:ASC')
            THEN Employee.EmpName
        WHEN (@sortKey = 'EmpDOB:ASC')
            THEN Employee.EmpDOB
    END ASC,
 
    CASE
        WHEN (@sortKey = 'EmpName:DESC')
            THEN Employee.EmpName
        WHEN (@sortKey = 'EmpDOB:DESC')
            THEN Employee.EmpDOB
    END DESC

Answer:  The problem here is that EmpName is a varchar(50), while EmpDOB is a datetime.  When using the CASE statement this way, it will properly execute multiple THENs only if the THEN statements are for the same data type, such as when all of them are varchar(50), or all are datetime. 

If they are not the same, they may compile but at execution time sometimes they will work and sometimes they will experience various errors.

It gets worse.  The various error messages are mysterious.  They will not give a clear indication of what is wrong and they may be different on different executions.  They will, however, mention problems converting data from one data type to another.  That's the clue.

This is what happens when we run the code, above.

 SET @sCol = 'EmpDOB'
 SET @sOrder = 'DESC'

This is what happens when we sort on the other column.

 SET @sCol = 'EmpName'
 SET @sOrder = 'DESC'

Msg 241, Level 16, State 1, Line 9

Syntax error converting datetime from character string.

Final version

So we will put differing data types into separate CASE statements.  This is our final version of the code:

DECLARE @sCol varchar(50)
    SET @sCol = 'EmpDOB'
DECLARE @sOrder varchar(4)
    SET @sOrder = 'DESC'
DECLARE @sortKey varchar(55)
    SET @sortKey = @sCol + ':' + @sOrder
SELECT
    Employee.EmpID,
    Employee.EmpName,
    Employee.EmpDOB
FROM Employee
ORDER BY
    CASE
        WHEN (@sortKey = 'EmpName:ASC')
            THEN Employee.EmpName
    END ASC,
    CASE
        WHEN (@sortKey = 'EmpName:DESC')
            THEN Employee.EmpName
    END DESC,
 
    CASE
        WHEN (@sortKey = 'EmpDOB:ASC')
            THEN Employee.EmpDOB
    END ASC,
    CASE
        WHEN (@sortKey = 'EmpDOB:DESC')
            THEN Employee.EmpDOB
    END DESC

Here's the output when we sort on the date of birth.
 
 SET @sCol = 'EmpDOB'
 SET @sOrder = 'DESC'
 

and when we sort on the Employee's name.

 SET @sCol = 'EmpName'
 SET @sOrder = 'ASC'

Conclusion

CASE statements can be effectively employed within an ORDER BY statement.  They just have some non-intuitive quirks that are not explained in the Help.  But if you follow these few simple guidelines you will be able to use CASE statements within ORDER BY statements with ease.