Dynamic sorting of database query results

In a public newsgroup, a common problem occurs: "How can we return a sorted output based on the parameters passed to the stored procedure ?". With the help of some high-level experts, I have developed several solutions to this problem.

1. Use if... else to execute the prepared Query

For most people, the first thought might be: execute one of several pre-written queries using the IF... else statement. For example, if you want to obtain a shipper sorting list from the northwind database query, the called code is issued to specify a column as a stored procedure parameter, the stored procedure sorts the output results based on the column. Listing 1 shows a possible implementation of this stored procedure (getsortedshippers stored procedure ).

[Listing 1: use if... else to execute one of multiple prepared Queries]

Create proc getsortedshippers

@ Ordseq as int

As

If @ ordseq = 1

Select * from shippers order by shipperid

Else if @ ordseq = 2

Select * from shippers order by companyName

Else if @ ordseq = 3

Select * from shippers order by phone

The advantage of this method is that the code is simple and easy to understand. The SQL Server query optimizer can create a query optimization plan for each SELECT query to ensure the optimal performance of the Code. The main drawback of this method is that if the query requirements change, you must modify multiple independent select queries-here there are three.

Ii. Using column names as parameters

Another option is to make the query receive a column name as 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 received parameters. Note that the expression in the order by clause does not appear in the select list. In ANSI SQL-92 standards, order by clauses do not allow expressions that are not specified in the select list, but ANSI SQL-99 standards allow. SQL Server Always allows this usage.

[Listing 2: using the column name as the parameter, the first attempt]

Create proc getsortedshippers

@ Colname as sysname

As

Select *

From Shippers

Order

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 and specify the shipperid column as a parameter:

Exec getsortedshippers 'shipperid'

Now everything is normal. However, when the companyName column is used as a parameter to call the stored procedure, it is no longer valid:

Exec getsortedshippers 'companyname'

Take a closer 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.

It shows that SQL Server tries to convert "Speedy Express" (nvarchar data type) into an integer-of course, this operation is impossible. The error occurs because, according to the "data type priority" rule, the data type with the highest priority in the case expression determines the data type returned by the expression. The "data type priority" rule can be found in SQL Server books online (BOL), which specifies that the priority of the int data type is higher than that of the nvarchar data type. The preceding Code requires SQL Server to output data by companyName. companyName is of the nvarchar data type. The return value of this case expression may be shipperid (INT type), companyName (nvarchar type), or phone (nvarchar type ). Because the int type has a higher priority, the Data Type returned by the case expression should be Int.

To avoid this conversion error, we can try to convert the shipperid to the varchar data type. After this method is used, nvarchar will be returned as the data type with the highest priority. Listing 3 shows the modified getsortedshippers stored procedure.

[Listing 3: using the column name as the parameter, the second attempt]

Alter proc getsortedshippers

@ Colname as sysname

As

Select *

From Shippers

Order

Case @ colname

When 'shipperid'

Then cast (shipperid as varchar (11 ))

When 'companyname'

Then companyName

When 'phone'

Then phone

Else null

End

Now, if we call the stored procedure using any of the three column names as parameters, the output result looks correct. It looks like the specified column correctly provides the sorting standard for the query output. However, this table has only three owners whose IDs are 1, 2, and 3. Suppose we add more owners to the table, as shown in Listing 4 (The shipperid column has the identity attribute, and SQL server automatically generates a value for this column ).

[Listing 4: insert some records to the shippers table]

Insert into shippers values ('shipper4', '(111) 222-9999 ')

Insert into shippers values ('shipper5', '(111) 222-8888 ')

Insert into shippers values ('shipper6', '(111) 222-7777 ')

Insert into shippers values ('shipper7', '(111) 222-6666 ')

Insert into shippers values ('shipper8', '(111) 222-5555 ')

Insert into shippers values ('shipper9', '(111) 222-4444 ')

Insert into shippers values ('shipper10', '(111) 222-3333 ')

Now call the stored procedure and specify the shipperid as the sorting column:

Exec getsortedshippers 'shipperid'

Table 1 shows the output of the stored procedure. The position of the record with shipperid equal to 10 is incorrect because the sorting output of this stored procedure is character sorting rather than integer sorting. When sorting by characters, 10 is arranged before 2, because the starting character of 10 is 1.

Table 1: query results with sorting errors

Shipperid companyName phone

1 speedy Express (503) 555-9831

10 shipper10 (111) 222-3333

2 United package (503) 555-3199

3 federal shipping (503) 555-9931

4 shipper4 (111) 222-9999

5 shipper5 (111) 222-8888

6 shipper6 (111) 222-7777

7 shipper7 (111) 222-6666

8 shipper8 (111) 222-5555

9 shipper9 (111) 222-4444

To solve this problem, we can use the front 0 to fill up the shipperid value so that the shipperid value has the same length. In this way, character-based sorting has the same output result as integer sorting. The modified stored procedure is shown in listing 5. The ten zeros are placed before the absolute value of the shipperid. In the result, the Code only uses the rightmost 10 characters. The sign function determines whether to add the plus sign (+) prefix before the positive number, or add the minus sign (-) prefix before the negative number. According to this method, the output result is always 11 characters, including a "+" or "-" character, leading character 0, and the absolute value of shipperid.

[Listing 5: using the column name as the parameter, the third attempt]

Alter proc getsortedshippers

@ Colname as sysname

As

Select *

From Shippers

Order

Case @ colname

When 'shipperid' then case sign (shipperid)

When-1 then '-'

When 0 then '+'

When 1 then '+'

Else null

End +

Right (replicate ('0', 10) +

Cast (ABS (shipperid) as varchar (10), 10)

When 'companyname' then companyName

When 'phone' then phone

Else null

End

If the value of shipperid is a positive number, it is unnecessary to add the symbol prefix. However, to make the solution applicable to as many ranges as possible, this example adds the symbol prefix. "-" Is prior to "+" during sorting, so it can be used for mixed sorting of positive and negative numbers.

Now, if one of the three column names is used as a parameter to call the stored procedure, the stored procedure can return results correctly. Richard Romley proposed a clever processing method, as shown in Listing 6. It no longer requires us to figure out the types of column data that may be involved. In this way, the order by clause is divided into three independent case expressions. Each expression processes a different column, avoiding the problem caused by the ability to return only one specific data type in case.

[Listing 6: Using column names as parameters, method proposed by Romley]

Alter proc getsortedshippers

@ Colname as sysname

As

Select *

From Shippers

Order

Case @ colname when 'shipperid'

Then shipperid else null end,

Case @ colname when 'companyname'

Then companyName else null end,

Case @ colname when 'phone'

Then phone else null end

By writing code in this way, SQL Server can return an appropriate data type for each case expression without data type conversion. However, it should be noted that the index can optimize the sorting operation only when the specified column does not need to be calculated.

3. Use column numbers as parameters

As shown in the first solution, you may prefer to use column numbers as parameters, instead of using the column name (the column number represents the number of the column you want to sort based on ). The basic idea of this method is the same as that of using column names as parameters: the case expression determines which column to use for sorting based on the specified column number. Listing 7 displays the modified getsortedshippers stored procedure.

[Listing 7: Using Column numbers as parameters]

Alter proc getsortedshippers

@ Colnumber as int

As

Select *

From Shippers

Order

Case @ colnumber

When 1 then case sign (shipperid)

When-1 then '-'

When 0 then '+'

When 1 then '+'

Else null

End +

Right (replicate ('0', 10) +

Cast (ABS (shipperid) as varchar (10), 10)

When 2 then companyName

When 3 then phone

Else null

End

Of course, you can also use the Richard method here to avoid the problem caused by the column data type in the order by clause. To sort the output by shipperid, you can call the modified getsortedshippers stored procedure as follows:

Exec getsortedshippers 1

4. Dynamic execution

With the dynamic execution technology, we can more easily compile the getsortedshippers stored procedure. When using this method, we only need to dynamically construct the SELECT statement, and then execute this select statement using the exec () command. If the parameter passed to the stored procedure is the column name, the stored procedure can be greatly shortened:

Alter proc getsortedshippers

@ Colname as sysname

As

Exec ('select * from shippers order by' +

@ Colname)

In SQL Server 2000 and 7.0, you can replace the exec () command with the system stored procedure sp_executesql. It indicates that using sp_executesql is more advantageous than using the exec () command. Generally, if the following three conditions are met, you can grant the stored procedure execution permission without granting the object permissions involved in the stored procedure: first, only the data manipulation language (DML) language (select, insert, update, delete) is used. Second, all referenced objects have the same owner as the stored procedure. Third, no dynamic command is used.

The above stored procedure cannot meet the third condition. In this case, you must explicitly grant select permissions to all users and groups that need to use stored procedures. If this is acceptable, there is no problem. Similarly, you can modify the stored procedure to accept a column number parameter, as shown in listing 8.

[Listing 8: Use the column number as the parameter for dynamic execution (long code method )]

Alter proc getsortedshippers

@ Colnumber as int

As

Declare @ cmd as varchar (8000)

Set @ cmd = 'select * from shippers order by' +

Case @ colnumber

When 1 then 'shipperid'

When 2 then 'companyname'

When 3 then 'phone'

Else 'null'

End

Exec (@ cmd)

Note that when you use a function, you should construct a SELECT statement in a variable instead of the exec () command. In this case, the case expression dynamically determines which column to use. There is also a simpler format where the T-SQL allows you to specify the position of the columns in the select list in the order by clause, as shown in listing 9. This format complies with the SQL-92 standard, but the ANSI SQL-99 standard does not support this format, so it is best not to use this format.

[Listing 9: The column number is used as a parameter for dynamic execution (method with short code )]

Alter proc getsortedshippers

@ Colnumber as int

As

Declare @ cmd as varchar (8000)

Set @ cmd = 'select * from shippers order by '+ Cast (@ colnumber as varchar (4 ))

Exec (@ cmd)

5. User-Defined Functions

If you are using SQL Server 2000 and want to write a user-defined function (UDF), this user-defined function accepts the column name or number as a parameter and returns the sorted result set, listing 10 shows that most programmers are the first choice.

[Listing 10: column name as parameter, using UDF]

Create Function ufn_getsortedshippers

(

@ Colname as sysname

)

Returns table

As

Return

Select *

From Shippers

Order

Case @ colname

When 'shipperid' then case sign (shipperid)

When-1 then '-'

When 0 then '+'

When 1 then '+'

Else null

End +

Right (replicate ('0', 10) +

Cast (ABS (shipperid)

Varchar (10), 10)

When 'companyname' then companyName

When 'phone' then phone

Else null

End

However, SQL server does not accept this function and returns the following error message:

Server: MSG 1033, Level 15, state 1, procedure ufn_getsortedshippers,

Line 24

The order by clause is invalid in views, inline functions, and

Subqueries, unless top is also specified.

Pay attention to "unless" in the error message ". SQL Server 2000 does not allow the order by clause to appear in views, embedded udfs, and subqueries, because they should all return a table and the table cannot specify the order of rows. However, if the top keyword is used, the order by clause will help determine the rows returned by the query. Therefore, if top is specified, you can also specify order. Because the order by clause is allowed in top udfs, you can use the following technique: Replace "select *" with "select Top 100 percent *". In this way, you can successfully construct a function that accepts the column name or number as a parameter and returns the sorting result.

The newly constructed function can be called as follows:

Select * From ufn_getsortedshippers ('shipperid ')

Now you have learned several methods to determine the record order in the query output using parameters. When writing applications that allow users to specify column sorting criteria for query results, you can use the techniques described in this article, using column names or numbers as parameters, construct various solutions using case expressions and dynamic execution capabilities.