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 sysname
AS
EXEC('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 24
The 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.