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

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


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


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


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)點(diǎn)是代碼很簡(jiǎn)單、很容易理解，SQL Server的查詢優(yōu)化器能夠?yàn)槊恳粋�(gè)SELECT查詢創(chuàng)建一個(gè)查詢優(yōu)化計(jì)劃，確保代碼具有最優(yōu)的性能。這種方法最主要的缺點(diǎn)是，如果查詢的要求發(fā)生了改變，你必須修改多個(gè)獨(dú)立的SELECT查詢——在這里是三個(gè)。


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

　　另外一個(gè)選擇是讓查詢以參數(shù)的形式接收一個(gè)列名字。Listing 2顯示了修改后的GetSortedShippers存儲(chǔ)過(guò)程。CASE表達(dá)式根據(jù)接收到的參數(shù)，確定SQL Server在ORDER BY子句中使用哪一個(gè)列值。注意，ORDER BY子句中的表達(dá)式并未在SELECT清單中出現(xiàn)。在ANSI SQL-92標(biāo)準(zhǔn)中，ORDER BY子句中不允許出現(xiàn)沒(méi)有在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)在，我們來(lái)試一下新的存儲(chǔ)過(guò)程，以參數(shù)的形式指定ShipperID列：


EXEC GetSortedShippers 'ShipperID'



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


EXEC GetSortedShippers 'CompanyName'



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


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


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

【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è)我們?cè)侔讶齻�(gè)列名字中的任意一個(gè)作為參數(shù)調(diào)用存儲(chǔ)過(guò)程，輸出結(jié)果看起來(lái)正確�？雌饋�(lái)就象指定的列正確地為查詢輸出提供了排序標(biāo)準(zhǔn)。但這個(gè)表只有三個(gè)貨主，它們的ID分別是1、2、3。假設(shè)我們把更多的貨主加入到表，如Listing 4所示（ShipperID列有IDENTITY屬性，SQL Server自動(dòng)為該列生成值）。


【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)用存儲(chǔ)過(guò)程，指定ShipperID作為排序列：


EXEC GetSortedShippers 'ShipperID'



　　表一顯示了存儲(chǔ)過(guò)程的輸出。ShipperID等于10的記錄位置錯(cuò)誤，因?yàn)檫@個(gè)存儲(chǔ)過(guò)程的排序輸出是字符排序，而不是整數(shù)排序。按照字符排序時(shí)，10排列在2的前面，因?yàn)?0的開(kāi)始字符是1。


表一：記錄排序錯(cuò)誤的查詢結(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


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

【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ù)，加上符號(hào)前綴就沒(méi)有必要，但為了讓方案適用于盡可能多的范圍，本例加上了符號(hào)前綴。排序時(shí)“-”在“+”的前面，所以它可以用于正、負(fù)數(shù)混雜排序的情況。


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


【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



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


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

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


【Listing 7：用列號(hào)作為參數(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ù)類型帶來(lái)的問(wèn)題。如果要根據(jù)ShipperID排序輸出，你可以按照下面的方式調(diào)用修改后的GetSortedShippers存儲(chǔ)過(guò)程：


EXEC GetSortedShippers 1


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

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


ALTER PROC GetSortedShippers

@ColName AS sysname

AS

EXEC('SELECT * FROM Shippers ORDER BY ' +

@ColName)



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


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


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


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


【Listing 9：列號(hào)作為參數(shù)，動(dòng)態(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，想要編寫(xiě)一個(gè)用戶定義的函數(shù)（UDF），這個(gè)用戶定義函數(shù)接受列的名字或編號(hào)為參數(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不接受這個(gè)函數(shù)，它將返回如下錯(cuò)誤信息：


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.



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


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


SELECT * FROM ufn_GetSortedShippers('ShipperID')



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