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 below:

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 set:

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: 

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.