Parameterizing Result Order

Return a sorted output based on a parameter

Editor's Note: Send your experts-only T-SQL tips to Itzik Ben-Gan at [email protected]. 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 5Syntax 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.

Using a Column Number as a Parameter

You might prefer to use the column number (i.e., a number representing the column by which you want to sort the result) as a parameter instead of using the column name, as in the first solution. The idea is basically the same as using the column name as a parameter: The CASE expression determines which column to use based on the column number you supplied. Listing 7 shows the code for the revised GetSortedShippers stored procedure.

Of course, you can use Richard's solution here as well to avoid handling the data types of the columns participating in the ORDER BY clause. If you want to sort the output by ShipperID, you would invoke the revised stored procedure as follows:

EXEC GetSortedShippers 1

Using Dynamic Execution

Using dynamic execution, you can write the GetSortedShippers stored procedure more easily. You can simply construct the SELECT statement dynamically and execute it through the EXEC() command. With the column name as the parameter, the stored procedure is much shorter:

ALTER PROC GetSortedShippers  @ColName AS sysnameASEXEC('SELECT * FROM Shippers ORDER BY ' + @ColName)

In SQL Server 2000 and 7.0, you can use the system stored procedure sp_ExecuteSQL in place of the EXEC() command. BOL lists the advantages of using sp_ExecuteSQL instead of the EXEC() command. But dynamic execution has its faults. In general, you can grant permissions to execute a stored procedure without granting permissions on the referenced objects if you meet three conditions. First, you use only Data Manipulation Language (DML) statements (i.e., SELECT, INSERT, UPDATE, DELETE); second, all the referenced objects have the same owner as the stored procedure; and third, you aren't using dynamic execution. The most recent version of the stored procedure doesn't meet the third condition. In this case, you need to grant explicit SELECT permissions on the Shippers table to all the users and groups that need to use the stored procedure. If that approach is acceptable, no problem. Similarly, you can revise the stored procedure to accommodate a column number as a parameter, as Listing 8 shows.

Note that you must construct the SELECT statement inside a variable instead of inside the EXEC() statement when you use a function. In this case, the CASE expression dynamically determines which column to use. You can use an even shorter form because T-SQL lets you specify in the ORDER BY clause the position of a column from the SELECT list, as Listing 9 shows. This form is ANSI SQL-92-compliant, but the ANSI SQL-99 standard doesn't support this form, so you might prefer not to use it.

Using a User-Defined Function

If you're using SQL Server 2000 and you want to write a user-defined function (UDF) that accepts the column name or number as a parameter and returns an ordered result, Listing 10, page 27, shows the solution that most programmers would probably think of as their first attempt. But SQL Server won't accept such a function and returns the following error:

Server: Msg 1033, Level 15, State 1, Procedure ufn_GetSortedShippers, Line 24The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Notice the "unless" in the error. SQL Server 2000 doesn't allow an ORDER BY clause in views, inline UDFs, derived tables, and subqueries because these should all return a table, which doesn't have a specific order to its rows. But when you use the TOP keyword, the ORDER BY clause might determine which rows the query returns. So you can specify an ORDER BY clause when you also specify TOP. Allowing the use of an ORDER BY clause in a UDF containing TOP lets you use a trick: Simply replace the code line

SELECT *

with

SELECT TOP 100 PERCENT *

and you can successfully create a function that accepts the column name or number as a parameter and returns an ordered result. You can invoke the new function this way:

SELECT * FROM ufn_GetSortedShippers('ShipperID')

Now you have seen several ways that you can use a parameter to determine the row order in a query's output. You can use the techniques in this article when you write applications that let the user choose the column by which to sort the output. You can use a column name or a column number as a parameter and construct solutions that use the CASE expression and dynamic execution.