In a public news group, a often problem is "How can I return a sorted output based on the parameter passed to the stored procedure?". Under the help of some high-level experts, I have organized several solutions for this problem. First, use IF ... Else to perform a preparatory query for most people, the first thought that it is perhaps: through the if ... ELSE statement, perform a few in advance in queries. For example, assume that you want to get a list of sorting lists from the NorthWind database, send a call to specify a column in the form of a stored procedure parameter, and the stored procedure is sorted according to this column. Listing 1 shows a possible implementation of this stored procedure (GetSortedshipPers stored procedure). [Listing 1: Use if ... Else to perform one of multiple preparatory good queries] Create Proc getSortedshipPers @ORDSEQ as int as if @ordseq = 1 Select * from shippers Order by Shipperid else if @ordseq = 2 Select * From shippers or @ordseq = 3 select * from shippers Order by Phone This method is simple, easy to understand, SQL Server's query optimizer can create a query optimization plan for each SELECT query, Make sure the code has the optimal performance. The most important disadvantage of this method is that if the requirements of the query have changed, you must modify multiple separate SELECT queries - here is three. Second, use the column name as the parameter to another option is to allow the query to receive a column name in the form of parameters. Listing 2 shows the modified GetSortedshipPers stored procedure. The CASE expression determines which column value used in the Order By clause based on the received parameters. Note that the expression in the ORDER BY clause is not appearing in the SELECT list. In the ANSI SQL-92 standard, the ORDER BY clause does not allow the expression specified in the SELECT inventory, but the ANSI SQL-99 standard is allowed. SQL Server has always allowed this usage. [Listing 2: use the column name as a parameter, the 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, specify the ShipperID column in the form of parameters: Exec getSortedshipPers 'ShipperId' At this time, everything is normal.
However, when our view calls the stored procedure as a parameter, it is no longer valid: Exec getSortedshipPers 'CompanyName' Take a closer information: Server: MSG 245, Level 16, State 1, Procedure Getsortshippers, Line 5 Syntax Error Converting It nvarchar value 'speedy expression' to a column of data type int. It shows that SQL Server is trying to convert "speedy express" into a integer value - of course, this operation is impossible to succeed. The reason for the error is that according to the "Data Type Priority" rule, the data type of the highest priority in the formula determines the data type of the expression return value. "Data Type Priority" rule can be found in SQL Server Books Online (BOL), which specifies the priority of the int data type than NVARCHAR data type. The previous code requires SQL Server to be sorted by CompanyName, and CompanyName is NVARCHAR data type. The return value of this CASE expression may be shipperid (int type), which may be CompanyName (NVARCHAR type), or phone (nVarchar type). Since the int type has a higher priority, the data type of the Case expression returns the value should be int. In order to avoid this conversion error, we can try to convert ShipperID to a VARCHAR data type. After this method, NVARCHAR will return as the highest priority data type. Listing 3 shows the modified GetSortedshipPers stored procedure. [Listing 3: with the column name as a parameter, a second attempt] ALTER PROC GetSortedShippers @ColName AS sysname AS SELECT * FROM Shippers ORDER BY CASE @ColName WHEN 'ShipperID' THEN CAST (ShipperID AS varchar (11)) WHEN 'CompanyName' Then CompanyName When 'Phone' Then Phone Else Null End Now, suppose we call any of three column names as a parameter, and the output results look correct. It seems that it provides a sorting standard for the specified column correctly for the query output. But this table has only three car owners, and their IDs are 1, 2, 3, respectively. Suppose we add more goods to the table, such as the Listing 4 (ShipperID column has an Identity property, SQL Server automatically generates the column).
[Listing 4: Insert some records to the shippers table] INSERT INTO Shippers Values ('Shipper4', ') INSERT INTO Shippers Values (' Shipper5 ',' (111) 222-8888 ') Insert Into Shippers Values ('Shipper6', '(111) 222-7777') Insert Into Shippers Values ('Shipper7', ') INSERT INTO SHIPPERS VALUES (' Shipper8 ',' (111) 222-5555 ' INSERT INTO shippers Values ('Shipper9', ') INSERT INTO Shippers Values (' Shipper10 ',' (111) 222-3333 ') Now calling stored procedures, specifying ShipperID as ranking sequence: Exec GetsortShipPpers: EXEC GETSORTEDSHIPPERS The 'ShipperID' table shows the output of the stored procedure. ShipperID is equal to 10 record position error, because the sorting output of this stored procedure is character sorting, not an integer sort. When sorted by character, 10 is arranged in front of 2 because 10 start characters are 1. Table 1: Recording Sort Unity Query SHIPPERID CompanyName Phone 1 Speedy ExpAname 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 In order to solve this problem, We can use the front 0 to make up the shipperid value, so that the ShipperID value has the same length. According to this method, character-based sorting has the same output result. The modified stored procedure is shown in Listing 5. Ten 0 is placed before the absolute value of Shipperid, and in the result, the code is just 10 characters on the rightmost. The SIGN function determines that the prefix ( ) prefix is added in front of the positive number, or the negative prefix is added in front of the negative number. According to this method, the output is always 11 characters, including a " " or "-" character, leading character 0, and absolute value of ShipperID.
[Listing 5: column name as a parameter, the third attempt] ALTER PROC GetSortedShippers @ColName AS sysname AS SELECT * FROM Shippers ORDER BY 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, there is no need to add a symbol prefix, but in order to make the scheme suitable for as many ranges as possible, this example adds a symbol prefix. When sorting "-" in front of " ", it can be used for the case where positive and negative mixing is sorted. Now, if we call a stored procedure as a parameter with one of the three column names, the stored procedure can return the result correctly. Richard Romley proposes a clever processing method, such as Listing 6. It no longer requires us to figure out the type of data that may be involved. This method divides the ORDER BY clause into three separate case expressions, each expression processes a different column, avoiding problems caused by the ability of Case only returns only a specific data type. [Listing 6: column name as a parameter, the method proposed Romley] ALTER PROC GetSortedShippers @ColName AS sysname AS SELECT * FROM Shippers ORDER BY CASE @ColName WHEN 'ShipperID' THEN ShipperID ELSE NULL END, CASE @ColName WHEN 'CompanyName' THEN COMPANYNAME ELSE NULL END, CASE @Colname When 'Phone' THEN ELSE NULL END Writing code according to this method, and SQL Server can return an appropriate data type for each Case expression, and does not need to perform data type conversion. However, it should be noted that the index can optimize the ordering operation only if the specified column does not need to be calculated. Third, using the list as the parameter, like the first scheme, you may prefer to use the number as the column as the parameters, not the name of the column (the number of the number, representing the column you want as sorting Number). The basic idea of this approach is the same as the use of column names as the parameters: Case expression determines which column to use based on the specified column number to sort. Listing 7 shows the modified GetSortShiPpers stored procedure.
[Listing 7: Use the list as the parameter] Alter Proc getSortshipPers @colnumber as int as selection * from shippers Order by casse @colnumber when 1 TEN Case Sign (Shipperid) WHEN -1 TEN '-' WHEN 0 TEN ' ' WHEN 1 THEN ' ELSE NULL END Right (Replicate (' 0 ', 10) Cast (ABS (SHIPPERID) As Varchar (10))), 10) WHEN 2 THEN Companyname When 3 Then Else Null End is of course, here you You can also use the Richard approach to avoid issues from the column data type in the order of the order of the ORDER BY. If you want to sort the output according to ShipperID, you can call the modified GetSortshipPers stored procedure as follows: Exec GetSortShippers 1 IV. Dynamic execution using dynamic execution technology, we can easily write the GetSortedshipPers stored procedure. When using this method, we only need to dynamically construct the SELECT statement and then use the exec () command to execute this SELECT statement. Assuming that the parameter passed to the stored procedure is the name of the column, the stored procedure can be greatly shortened: alter proc getsortshippers @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 to replace the exec () command. BOL illustrates the use of sp_executesql than using an Exec () command more favorable place. Generally, if the following three conditions are met, you can grant the permission to perform the stored procedure without granting the stored procedure: First, just use the Data Manipulation Language (DML) language (ie SELECT, INSERT, UPDATE) , DELETE); Second, all referenced objects have the same owner as the stored procedure; third, no dynamic commands are used. The above stored procedure cannot meet the third condition. In this case, you have to grant SELECT permissions for all users and groups that need to use stored procedures. If this is acceptable, everything does not have problems. Similarly, you can modify the stored procedure so that it accepts a column number parameter, such as Listing 8.
[Listing 8: Use the list as a parameter, dynamic execution (code longer)] ALTER proc getsortshipPers @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 TEN 'Phone' Else 'Null' End EXEC (@cmd) Note that when you use a function, you should be in a variable rather than an exec () command Construct the SELECT statement. At this point, the CASE expression dynamically determines which one is used. There is also a shorter format, T-SQL allows you to specify a location in the SELECT list in the ORDER BY clause, such as Listing 9. This format complies with SQL-92 standards, but the ANSI SQL-99 standard does not support this format, so it is best not to use this format. [Listing 9: List, dynamic execution (short method of code)] ALTER proc getsortshipPers @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 use SQL Server 2000, you want to write a user-defined function (UDF), this user-defined function accepts the name or number of the column or number The parameter, the result set of returns, Listing 10 shows most programmers as the first choice.
