Q: I'am 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?
A: This is a typical requirement
and the usage of CASE with Order by is rather unique. Look at the explanation
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 loopholes or pitfalls and
explains how using multiple CASE statements can help you get the results you
want. CASE expression by itself is powerful and helps you do a number of
operations. I would take each section of using case. The following SQL script
shows how you might try to use a CASE expression to dynamically order a result
DECLARE @OrderByOption int
@OrderByOption = 1 THEN EmployeeID
@OrderByOption = 2 THEN LastName
Conceptually, the query offers the ability to order by
either the EmployeeID column or the LastName column based on the current value
of @OrderByOption. The above statement attempts to order by LastName, but
produces the error, "Server: Msg 245, Level 16, State 1, Line 4 Syntax error
converting the nvarchar value 'Alice Mutton' to a column of data type int."
However, the script works if the value of @OrderByOption is set to 1.
To understand why the query 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. (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: EmployeeID,
which is an integer data type, and LastName, 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 LastName 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.
You can work around this problem by using multiple
CASE statements, as the following example shows:
DECLARE @OrderByOption int
SET @OrderByOption = 2
CASE WHEN @OrderByOption = 1 THEN EmployeeID END,
CASE WHEN @OrderByOption = 2 THEN LastName END
This example works properly whether you're sorting by
EmployeesID or LastName 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.