在公共新聞組中，一個經(jīng)常出現(xiàn)的問題是“怎樣才能根據(jù)傳遞給存儲過程的參數(shù)返回一個排序的輸出？”。在一些高水平專家的幫助之下，我整理出了這個問題的幾種解決方案。

一、用IF...ELSE執(zhí)行預(yù)先編寫好的查詢


　　對于大多數(shù)人來說，首先想到的做法也許是：通過IF...ELSE語句，執(zhí)行幾個預(yù)先編寫好的查詢中的一個。例如，假設(shè)要從Northwind數(shù)據(jù)庫查詢得到一個貨主（Shipper）的排序列表，發(fā)出調(diào)用的代碼以存儲過程參數(shù)的形式指定一個列，存儲過程根據(jù)這個列排序輸出結(jié)果。Listing 1顯示了這種存儲過程的一個可能的實現(xiàn)（GetSortedShippers存儲過程）。


【Listing 1: 用IF...ELSE執(zhí)行多個預(yù)先編寫好的查詢中的一個】


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



　　這種方法的優(yōu)點是代碼很簡單、很容易理解，SQL Server的查詢優(yōu)化器能夠為每一個SELECT查詢創(chuàng)建一個查詢優(yōu)化計劃，確保代碼具有最優(yōu)的性能。這種方法最主要的缺點是，如果查詢的要求發(fā)生了改變，你必須修改多個獨立的SELECT查詢——在這里是三個。


二、用列名字作為參數(shù)

　　另外一個選擇是讓查詢以參數(shù)的形式接收一個列名字。Listing 2顯示了修改后的GetSortedShippers存儲過程。CASE表達(dá)式根據(jù)接收到的參數(shù)，確定SQL Server在ORDER BY子句中使用哪一個列值。注意，ORDER BY子句中的表達(dá)式并未在SELECT清單中出現(xiàn)。在ANSI SQL-92標(biāo)準(zhǔn)中，ORDER BY子句中不允許出現(xiàn)沒有在SELECT清單中指定的表達(dá)式，但ANSI SQL-99標(biāo)準(zhǔn)允許。SQL Server一直允許這種用法。


【Listing 2：用列名字作為參數(shù)，第一次嘗試】


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



　　現(xiàn)在，我們來試一下新的存儲過程，以參數(shù)的形式指定ShipperID列：


EXEC GetSortedShippers 'ShipperID'



　　此時一切正常。但是，當(dāng)我們視圖把CompanyName列作為參數(shù)調(diào)用存儲過程時，它不再有效：


EXEC GetSortedShippers 'CompanyName'



　　仔細(xì)看一下錯誤信息：


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.



　　它顯示出，SQL Server試圖把“Speedy Express”（nvarchar數(shù)據(jù)類型）轉(zhuǎn)換成一個整數(shù)值——當(dāng)然，這個操作是不可能成功的。出現(xiàn)錯誤的原因在于，按照“數(shù)據(jù)類型優(yōu)先級”規(guī)則，CASE表示式中最高優(yōu)先級的數(shù)據(jù)類型決定了表達(dá)式返回值的數(shù)據(jù)類型。“數(shù)據(jù)類型優(yōu)先級”規(guī)則可以在SQL Server Books Online（BOL）找到，它規(guī)定了int數(shù)據(jù)類型的優(yōu)先級要比nvarchar數(shù)據(jù)類型高。前面的代碼要求SQL Server按照CompanyName排序輸出，CompanyName是nvarchar數(shù)據(jù)類型。這個CASE表達(dá)式的返回值可能是ShipperID（int類型），可能是CompanyName（nvarchar類型），或Phone（nvarchar類型）。由于int類型具有較高的優(yōu)先級，因此CASE表達(dá)式返回值的數(shù)據(jù)類型應(yīng)該是int。


為了避免出現(xiàn)這種轉(zhuǎn)換錯誤，我們可以嘗試把ShipperID轉(zhuǎn)換成varchar數(shù)據(jù)類型。采用這種方法之后，nvarchar將作為最高優(yōu)先級的數(shù)據(jù)類型被返回。Listing 3顯示了修改后的GetSortedShippers存儲過程。

【Listing 3：用列名字作為參數(shù)，第二次嘗試】


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



　　現(xiàn)在，假設(shè)我們再把三個列名字中的任意一個作為參數(shù)調(diào)用存儲過程，輸出結(jié)果看起來正確?？雌饋砭拖笾付ǖ牧姓_地為查詢輸出提供了排序標(biāo)準(zhǔn)。但這個表只有三個貨主，它們的ID分別是1、2、3。假設(shè)我們把更多的貨主加入到表，如Listing 4所示（ShipperID列有IDENTITY屬性，SQL Server自動為該列生成值）。


【Listing 4：向Shippers表插入一些記錄】


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')



　　現(xiàn)在調(diào)用存儲過程，指定ShipperID作為排序列：


EXEC GetSortedShippers 'ShipperID'



　　表一顯示了存儲過程的輸出。ShipperID等于10的記錄位置錯誤，因為這個存儲過程的排序輸出是字符排序，而不是整數(shù)排序。按照字符排序時，10排列在2的前面，因為10的開始字符是1。


表一：記錄排序錯誤的查詢結(jié)果


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


為了解決這個問題，我們可以用前置的0補足ShipperID值，使得ShipperID值都有同樣的長度。按照這種方法，基于字符的排序具有和整數(shù)排序同樣的輸出結(jié)果。修改后的存儲過程如Listing 5所示。十個0被置于ShipperID的絕對值之前，而在結(jié)果中，代碼只是使用最右邊的10個字符。SIGN函數(shù)確定在正數(shù)的前面加上加號（+）前綴，還是在負(fù)數(shù)的前面加上負(fù)號（-）前綴。按照這種方法，輸出結(jié)果總是有11個字符，包含一個“+”或“-”字符、前導(dǎo)的字符0以及ShipperID的絕對值。

【Listing 5：用列名字作為參數(shù)，第三次嘗試】


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



　　如果ShipperID的值都是正數(shù)，加上符號前綴就沒有必要，但為了讓方案適用于盡可能多的范圍，本例加上了符號前綴。排序時“-”在“+”的前面，所以它可以用于正、負(fù)數(shù)混雜排序的情況。


　　現(xiàn)在，如果我們用任意三個列名字之一作為參數(shù)調(diào)用存儲過程，存儲過程都能夠正確地返回結(jié)果。Richard Romley提出了一種巧妙的處理方法，如Listing 6所示。它不再要求我們搞清楚可能涉及的列數(shù)據(jù)類型。這種方法把ORDER BY子句分成三個獨立的CASE表達(dá)式，每一個表達(dá)式處理一個不同的列，避免了由于CASE只返回一種特定數(shù)據(jù)類型的能力而導(dǎo)致的問題。


【Listing 6：用列名字作為參數(shù)，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 Phone ELSE NULL END



　　按照這種方法編寫代碼，SQL Server能夠為每一個CASE表達(dá)式返回恰當(dāng)?shù)臄?shù)據(jù)類型，而且無需進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。但應(yīng)該注意的是，只有當(dāng)指定的列不需要進(jìn)行計算時，索引才能夠優(yōu)化排序操作。


三、用列號作為參數(shù)

　　就象第一個方案所顯示地那樣，你也許更喜歡用列的編號作為參數(shù)，而不是使用列的名字（列的編號即一個代表你想要作為排序依據(jù)的列的數(shù)字）。這種方法的基本思想與使用列名字作為參數(shù)的思想一樣：CASE表達(dá)式根據(jù)指定的列號確定使用哪一個列進(jìn)行排序。Listing 7顯示了修改后的GetSortedShippers存儲過程。


【Listing 7：用列號作為參數(shù)】


ALTER PROC GetSortedShippers

@ColNumber AS int

AS


SELECT *

FROM Shippers

ORDER BY

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



　　當(dāng)然，在這里你也可以使用Richard的方法，避免ORDER BY子句中列數(shù)據(jù)類型帶來的問題。如果要根據(jù)ShipperID排序輸出，你可以按照下面的方式調(diào)用修改后的GetSortedShippers存儲過程：


EXEC GetSortedShippers 1


四、動態(tài)執(zhí)行

　　使用動態(tài)執(zhí)行技術(shù)，我們能夠更輕松地編寫出GetSortedShippers存儲過程。使用這種方法時，我們只需動態(tài)地構(gòu)造出SELECT語句，然后用EXEC()命令執(zhí)行這個SELECT語句。假設(shè)傳遞給存儲過程的參數(shù)是列的名字，存儲過程可以大大縮短：


ALTER PROC GetSortedShippers

@ColName AS sysname

AS

EXEC('SELECT * FROM Shippers ORDER BY ' +

@ColName)



　　在SQL Server 2000和7.0中，你可以用系統(tǒng)存儲過程sp_ExecuteSQL替代Exec()命令。BOL說明了使用sp_ExecuteSQL比使用Exec()命令更有利的地方。一般地，如果滿足以下三個條件，你能夠在不授予存儲過程所涉及對象權(quán)限的情況下，授予執(zhí)行存儲過程的權(quán)限：首先，只使用Data Manipulation Language（DML）語言（即SELECT，INSERT，UPDATE，DELETE）；其次，所有被引用的對象都有與存儲過程同樣的所有者；第三，沒有使用動態(tài)命令。


　　上面的存儲過程不能滿足第三個條件。在這種情況下，你必須為所有需要使用存儲過程的用戶和組顯式地授予Shippers表的SELECT權(quán)限。如果這一點可以接受的話，一切不存在問題。類似地，你可以修改存儲過程，使它接受一個列號參數(shù)，如Listing 8所示。


【Listing 8：用列號作為參數(shù)，動態(tài)執(zhí)行（代碼較長的方法）】


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)



　　注意，當(dāng)你使用了函數(shù)時，你應(yīng)該在一個變量而不是EXEC()命令內(nèi)構(gòu)造SELECT語句。此時，CASE表達(dá)式動態(tài)地確定使用哪一個列。還有一種更簡短的格式，T-SQL允許在ORDER BY子句中指定SELECT清單中列的位置，如Listing 9所示。這種格式遵從了SQL-92標(biāo)準(zhǔn)，但ANSI SQL-99標(biāo)準(zhǔn)不支持這種格式，所以最好不要使用這種格式。


【Listing 9：列號作為參數(shù)，動態(tài)執(zhí)行（代碼較短的方法）】


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)


五、用戶定義函數(shù)

　　如果你使用的是SQL Server 2000，想要編寫一個用戶定義的函數(shù)（UDF），這個用戶定義函數(shù)接受列的名字或編號為參數(shù)、返回排序的結(jié)果集，Listing 10顯示了大多數(shù)程序員當(dāng)成第一選擇的方法。


【Listing 10：列名字作為參數(shù)，使用UDF】


CREATE FUNCTION ufn_GetSortedShippers

(

@ColName AS sysname

)

RETURNS TABLE

AS


RETURN

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



　　但是，SQL Server不接受這個函數(shù)，它將返回如下錯誤信息：


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.



　　注意錯誤信息中的“unless”。SQL Server 2000不允許在視圖、嵌入式UDF、子查詢中出現(xiàn)ORDER BY子句，因為它們都應(yīng)該返回一個表，表不能指定行的次序。然而，如果使用了TOP關(guān)鍵詞，ORDER BY子句將幫助確定查詢所返回的行。因此，如果指定了TOP，你還可以同時指定ORDER BY。由于在帶有TOP的UDF中允許使用ORDER BY子句，你可以使用一個技巧：把“SELECT *”替換成“SELECT TOP 100 PERCENT *”。這樣，你就能夠成功地構(gòu)造出一個接受列名字或編號為參數(shù)、返回排序結(jié)果的函數(shù)。


　　新構(gòu)造的函數(shù)可以按照如下方式調(diào)用：


SELECT * FROM ufn_GetSortedShippers('ShipperID')



　　現(xiàn)在，你已經(jīng)了解了幾種用參數(shù)確定查詢輸出中記錄次序的方法。在編寫那些允許用戶指定查詢結(jié)果排序標(biāo)準(zhǔn)的列的應(yīng)用程序時，你可以使用本文介紹的各種技術(shù)，用列名字或編號作為參數(shù)，構(gòu)造出使用CASE表達(dá)式和動態(tài)執(zhí)行能力的各種方案。