Using CASE to Order a Result Set

Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at [email protected].

I'm trying to use a CASE expression in an ORDER BY clause to return a result set in different sort orders based on a parameter passed to the procedure. How can I get the result set ordered as I want?

Dynamically ordering a result set based on the evaluation of a CASE expression is a powerful technique for ordering your data. The following example shows some possible gotchas and explains how using multiple CASE statements can help you get the results you want.

The SQL script in Listing 1 shows how you might try to use a CASE expression to dynamically order a result set. Conceptually, the query offers the ability to order by either the ProductId column or the ProductName column based on the current value of @OrderByOption. Listing 1's script attempts to order by ProductName, but produces the following error:

Server: Msg 245, Level 16, State 1, Line 4Syntax error converting the nvarchar value 'Alice Mutton' to a column of data type int.

However, the script in Listing 1 works if the value of @OrderByOption is set to 1. To understand why the query in Listing 1 works when the value for @OrderByOption is set to 1 but doesn't work when the value is set to 2, you need to recognize that the two THEN conditions of the CASE statement reference expressions of different data types. In this case, SQL Server implicitly converts the data type for the entire CASE expression to the data type in the THEN clause that has the highest order of data-type precedence. (For information about data-type precedence and conversion, see the SQL Server Books Online (BOL) topic "Data Type Precedence.") In this example, the CASE statement has two possible values that follow a THEN clause: ProductId, which is an integer data type, and ProductName, which is an nvarchar data type. The integer data type has a higher precedence than the nvarchar data type, so SQL Server attempts to cast the ProductName expression as an integer if you try to order by that column. Such a conversion isn't allowed, so SQL Server generates the above error.

However, you can work around this problem by using multiple CASE statements, as Listing 2 shows. The example in Listing 2 works properly whether you're sorting by ProductId or ProductName because it uses two separate CASE expressions that each have a single THEN clause, so you don't need to convert different values to different data types based on order-of-precedence rules.