Demystifying the use of CASE in an ORDER BY statement

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.

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.

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.

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.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.