SQL Server Magazine

Editor's Note: Send your experts-only T-SQL tips to Itzik Ben-Gan at blackbelt@sqlmag.com. If we use your tip in the magazine, you'll receive $100 and an exclusive T-SQL Black Belt shirt.

Parameterizing Result Order
One question that often appears in public newsgroups is "How can you return sorted output based on a parameter that you pass to a stored procedure?" I've gathered a few solutions to this problem based on the ideas of several talented individuals. SQL Server MVPs Bruce P. Margolin and Neil Pike provided most of the ideas for this article, and Richard Romley provided an ingenious solution to one of the problems. You can also find a few of the solutions I present, along with solutions to many other SQL Server-related problems, in Neil Pike's book SQL Server: Common Problems, Tested Solutions (Apress, 2000).

Using IF...ELSE to Execute a Pre-Programmed Query
Using an IF...ELSE statement to execute one of a few pre-programmed queries is the option that probably comes to mind first for most people. For example, suppose you want to provide a sorted list of shippers from the Shippers table in the Northwind database, where the code would pass the column by which you want to sort the result to the stored procedure as a parameter. Your stored procedure would probably look like the GetSortedShippers procedure in Listing 1. The advantages of this option are that the code is straightforward and easy to understand and the SQL Server query optimizer can create an optimized query plan for each SELECT query, thus ensuring maximal performance. The primary disadvantage is that you have to maintain several separate SELECT queries—in this case, three—if the reporting requirements change.

Using a Column Name as a Parameter
Another option is to let your query accept the column name as a parameter. The code in Listing 2 shows the revised GetSortedShippers stored procedure. The CASE expression determines which of the columns' values SQL Server will use in the ORDER BY clause, based on the value of the supplied parameter. Note that the expression in the ORDER BY clause isn't in the SELECT list. The ANSI SQL-92 standard doesn't let you use an expression in the ORDER BY clause if you don't also specify the expression in the SELECT list, but the ANSI SQL-99 standard does. Note that SQL Server has always allowed this use.

Now let's try the new stored procedure, providing the ShipperID column as the parameter:

EXEC GetSortedShippers 'ShipperID'

All looks well so far, but when you try invoking this procedure with the CompanyName column as the parameter, it doesn't work:

EXEC GetSortedShippers 'CompanyName'

A close look at the error message

Server: Msg 245, Level 16, State 1, Procedure GetSortedShippers, Line 5
Syntax error converting the nvarchar value 'Speedy
 Express' to a column of data type int.

reveals that SQL Server attempted to convert the value 'Speedy Express' (which is of the nvarchar data type) to an integer data type—an action that is, of course, impossible. You get an error because the highest-precedence data type in a CASE expression determines the expression's return type according to the Data Type Precedence rules. These rules, which you can find in SQL Server Books Online (BOL), give the int data type a higher precedence than the nvarchar data type. The code asked SQL Server to sort the output by CompanyName, which is of the nvarchar data type. This CASE expression can return either the ShipperID (int), the CompanyName (nvarchar), or the Phone (nvarchar). Because the int data type has the highest precedence, the data type of the CASE expression's return value must be int.

To avoid this conversion error, you can try to convert the ShipperID to a varchar data type. That way, nvarchar will be the highest-precedence data type returned. Listing 3 shows the revised GetSortedShippers stored procedure. If you now try to invoke the stored procedure with either of the three possible column names as a parameter, the output looks right. It looks as if the requested column provided the sort criteria for the output. But the table has only three shippers, with IDs 1, 2, and 3. Suppose you add seven more shippers to the table, as Listing 4 shows. (The ShipperID column has the IDENTITY property, so SQL Server automatically generates values for that column.)

Now invoke the stored procedure, supplying the ShipperID as the sort column:

EXEC GetSortedShippers 'ShipperID'

Table 1 shows this stored procedure's output. The entry for Shipper10 is misplaced because the code produced a character-based, not integer-based, sort. With strings, 10 sorts ahead of 2 because it starts with the character 1. To overcome this problem, you can pad the ShipperID values with leading zeros and a sign to make them all the same length. That way, a character-based sort would yield the same result as an integer sort. The revised stored procedure is in Listing 5. Ten zeros prefix the absolute value of ShipperID, and from the result, the code uses only the rightmost 10 characters. The SIGN() function determines whether to prefix the result with a plus sign (+) for nonnegative values or a minus (-) sign for negative values. This way, the result will always have 11 characters holding a + or a - sign, leading zeros, and the absolute value of ShipperID. Prefixing with a sign isn't necessary if you'll have no negative shipper IDs, but I added the sign to keep the solution as general-purpose as possible. The - sorts ahead of the +, so it works for this case.

If you now use any of the three column names as a parameter to invoke the stored procedure, the procedure works like a charm. Richard Romley authored the ingenious solution that Listing 6 shows; it doesn't require you to be aware of the possible data types involved. By breaking the ORDER BY clause into three separate CASE expressions, each of which handles a different column, Richard avoided the problems inherent in CASE's ability to return a value of only one specific data type. If you use this construction, SQL Server can return an appropriate data type for each CASE expression without needing to transform data types. Note, however, that an index optimizes the sort operation only when the specified column has no computations.