Dynamic ordering of database query results (2)

Dynamic | sort | data | Database Two, use column name as parameter


Another option is to have the query receive a column name in the form of a parameter. Listing 2 shows the modified getsortedshippers stored procedure. The case expression determines which column value SQL Server uses in the ORDER BY clause, based on the parameters received. Note that the expression in the ORDER BY clause does not appear in the select list. In the ANSI SQL-92 standard, expressions not specified in the select list are not allowed in the ORDER BY clause, but the ANSI SQL-99 standard allows. SQL Server has been allowing this usage.



"Listing 2: Use column name as parameter, first attempt"


CREATE PROC Getsortedshippers

@ColName as sysname

As


SELECT *

From shippers

ORDER BY

Case @ColName

When ' ShipperID ' THEN ShipperID

When ' CompanyName ' THEN CompanyName

When ' phone ' THEN phone

ELSE NULL

End





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



EXEC getsortedshippers ' ShipperID '





Everything is fine at this time. However, when we view the CompanyName column as an argument to the stored procedure, it is no longer valid:



EXEC getsortedshippers ' CompanyName '





Take a closer look at the error message:



Server:msg 245, level, State 1, Procedure Getsortedshippers, line 5

Syntax error converting the nvarchar value ' Speedy

Express ' to a column of data type int.





It shows that SQL Server is trying to convert "Speedy Express" (nvarchar data type) to an integer value--Of course, this operation is unlikely to succeed. The error occurs because, according to the data type precedence rule, the data type of the highest priority in the case representation determines the data type of the return value of the expression. The data type precedence rule can be found in SQL Server books Online (BOL), which prescribes that the int data type has a higher precedence than the nvarchar data type. The preceding code requires SQL Server to sort the output according to CompanyName, CompanyName is the nvarchar data type. The return value of this case expression may be shipperid (int type), possibly companyname (nvarchar type), or phone (nvarchar type). Because the int type has a higher priority, the data type of the case expression return value should be int.