|
在公共新聞組中，一個經常出現的問題是“怎樣才能根據傳遞給存儲過程的參數返回一個排序的輸出？”。在一些高水平專家的幫助之下，我整理出了這個問題的幾種解決方案。
一、用IF...ELSE執行預先編寫好的查詢
對于大多數人來說，首先想到的做法也許是：通過IF...ELSE語句，執行幾個預先編寫好的查詢中的一個。例如，假設要從Northwind數據庫查詢得到一個貨主（Shipper）的排序列表，發出調用的代碼以存儲過程參數的形式指定一個列，存儲過程根據這個列排序輸出結果。Listing 1顯示了這種存儲過程的一個可能的實現（GetSortedShippers存儲過程）。
【Listing 1: 用IF...ELSE執行多個預先編寫好的查詢中的一個】
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
這種方法的優點是代碼很簡單、很容易理解，SQL Server的查詢優化器能夠為每一個SELECT查詢創建一個查詢優化計劃，確保代碼具有最優的性能。這種方法最主要的缺點是，如果查詢的要求發生了改變，你必須修改多個獨立的SELECT查詢——在這里是三個。
二、用列名字作為參數
另外一個選擇是讓查詢以參數的形式接收一個列名字。Listing 2顯示了修改后的GetSortedShippers存儲過程。CASE表達式根據接收到的參數，確定SQL Server在ORDER BY子句中使用哪一個列值。注意，ORDER BY子句中的表達式并未在SELECT清單中出現。在ANSI SQL-92標準中，ORDER BY子句中不允許出現沒有在SELECT清單中指定的表達式，但ANSI SQL-99標準允許。SQL Server一直允許這種用法。
【Listing 2：用列名字作為參數，第一次嘗試】
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
現在，我們來試一下新的存儲過程，以參數的形式指定ShipperID列：
EXEC GetSortedShippers 'ShipperID'
此時一切正常。但是，當我們視圖把CompanyName列作為參數調用存儲過程時，它不再有效：
EXEC GetSortedShippers 'CompanyName'
仔細看一下錯誤信息：
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數據類型）轉換成一個整數值——當然，這個操作是不可能成功的。出現錯誤的原因在于，按照“數據類型優先級”規則，CASE表示式中最高優先級的數據類型決定了表達式返回值的數據類型。“數據類型優先級”規則可以在SQL Server Books Online（BOL）找到，它規定了int數據類型的優先級要比nvarchar數據類型高。前面的代碼要求SQL Server按照CompanyName排序輸出，CompanyName是nvarchar數據類型。這個CASE表達式的返回值可能是ShipperID（int類型），可能是CompanyName（nvarchar類型），或Phone（nvarchar類型）。由于int類型具有較高的優先級，因此CASE表達式返回值的數據類型應該是int。
為了避免出現這種轉換錯誤，我們可以嘗試把ShipperID轉換成varchar數據類型。采用這種方法之后，nvarchar將作為最高優先級的數據類型被返回。Listing 3顯示了修改后的GetSortedShippers存儲過程。
【Listing 3：用列名字作為參數，第二次嘗試】
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
現在，假設我們再把三個列名字中的任意一個作為參數調用存儲過程，輸出結果看起來正確。看起來就象指定的列正確地為查詢輸出提供了排序標準。但這個表只有三個貨主，它們的ID分別是1、2、3。假設我們把更多的貨主加入到表，如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')
現在調用存儲過程，指定ShipperID作為排序列：
EXEC GetSortedShippers 'ShipperID'
表一顯示了存儲過程的輸出。ShipperID等于10的記錄位置錯誤，因為這個存儲過程的排序輸出是字符排序，而不是整數排序。按照字符排序時，10排列在2的前面，因為10的開始字符是1。
表一：記錄排序錯誤的查詢結果
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值都有同樣的長度。按照這種方法，基于字符的排序具有和整數排序同樣的輸出結果。修改后的存儲過程如Listing 5所示。十個0被置于ShipperID的絕對值之前，而在結果中，代碼只是使用最右邊的10個字符。SIGN函數確定在正數的前面加上加號（+）前綴，還是在負數的前面加上負號（-）前綴。按照這種方法，輸出結果總是有11個字符，包含一個“+”或“-”字符、前導的字符0以及ShipperID的絕對值。
【Listing 5：用列名字作為參數，第三次嘗試】
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的值都是正數，加上符號前綴就沒有必要，但為了讓方案適用于盡可能多的范圍，本例加上了符號前綴。排序時“-”在“+”的前面，所以它可以用于正、負數混雜排序的情況。
現在，如果我們用任意三個列名字之一作為參數調用存儲過程，存儲過程都能夠正確地返回結果。Richard Romley提出了一種巧妙的處理方法，如Listing 6所示。它不再要求我們搞清楚可能涉及的列數據類型。這種方法把ORDER BY子句分成三個獨立的CASE表達式，每一個表達式處理一個不同的列，避免了由于CASE只返回一種特定數據類型的能力而導致的問題。
【Listing 6：用列名字作為參數，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表達式返回恰當的數據類型，而且無需進行數據類型轉換。但應該注意的是，只有當指定的列不需要進行計算時，索引才能夠優化排序操作。
三、用列號作為參數
就象第一個方案所顯示地那樣，你也許更喜歡用列的編號作為參數，而不是使用列的名字（列的編號即一個代表你想要作為排序依據的列的數字）。這種方法的基本思想與使用列名字作為參數的思想一樣：CASE表達式根據指定的列號確定使用哪一個列進行排序。Listing 7顯示了修改后的GetSortedShippers存儲過程。
【Listing 7：用列號作為參數】
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
當然，在這里你也可以使用Richard的方法，避免ORDER BY子句中列數據類型帶來的問題。如果要根據ShipperID排序輸出，你可以按照下面的方式調用修改后的GetSortedShippers存儲過程：
EXEC GetSortedShippers 1
四、動態執行
使用動態執行技術，我們能夠更輕松地編寫出GetSortedShippers存儲過程。使用這種方法時，我們只需動態地構造出SELECT語句，然后用EXEC()命令執行這個SELECT語句。假設傳遞給存儲過程的參數是列的名字，存儲過程可以大大縮短：
ALTER PROC GetSortedShippers
@ColName AS sysname
AS
EXEC('SELECT * FROM Shippers ORDER BY ' +
@ColName)
在SQL Server 2000和7.0中，你可以用系統存儲過程sp_ExecuteSQL替代Exec()命令。BOL說明了使用sp_ExecuteSQL比使用Exec()命令更有利的地方。一般地，如果滿足以下三個條件，你能夠在不授予存儲過程所涉及對象權限的情況下，授予執行存儲過程的權限：首先，只使用Data Manipulation Language（DML）語言（即SELECT，INSERT，UPDATE，DELETE）；其次，所有被引用的對象都有與存儲過程同樣的所有者；第三，沒有使用動態命令。
上面的存儲過程不能滿足第三個條件。在這種情況下，你必須為所有需要使用存儲過程的用戶和組顯式地授予Shippers表的SELECT權限。如果這一點可以接受的話，一切不存在問題。類似地，你可以修改存儲過程，使它接受一個列號參數，如Listing 8所示。
【Listing 8：用列號作為參數，動態執行（代碼較長的方法）】
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)
注意，當你使用了函數時，你應該在一個變量而不是EXEC()命令內構造SELECT語句。此時，CASE表達式動態地確定使用哪一個列。還有一種更簡短的格式，T-SQL允許在ORDER BY子句中指定SELECT清單中列的位置，如Listing 9所示。這種格式遵從了SQL-92標準，但ANSI SQL-99標準不支持這種格式，所以最好不要使用這種格式。
【Listing 9：列號作為參數，動態執行（代碼較短的方法）】
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)
五、用戶定義函數
如果你使用的是SQL Server 2000，想要編寫一個用戶定義的函數（UDF），這個用戶定義函數接受列的名字或編號為參數、返回排序的結果集，Listing 10顯示了大多數程序員當成第一選擇的方法。
【Listing 10：列名字作為參數，使用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不接受這個函數，它將返回如下錯誤信息：
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、子查詢中出現ORDER BY子句，因為它們都應該返回一個表，表不能指定行的次序。然而，如果使用了TOP關鍵詞，ORDER BY子句將幫助確定查詢所返回的行。因此，如果指定了TOP，你還可以同時指定ORDER BY。由于在帶有TOP的UDF中允許使用ORDER BY子句，你可以使用一個技巧：把“SELECT *”替換成“SELECT TOP 100 PERCENT *”。這樣，你就能夠成功地構造出一個接受列名字或編號為參數、返回排序結果的函數。
新構造的函數可以按照如下方式調用：
SELECT * FROM ufn_GetSortedShippers('ShipperID')
現在，你已經了解了幾種用參數確定查詢輸出中記錄次序的方法。在編寫那些允許用戶指定查詢結果排序標準的列的應用程序時，你可以使用本文介紹的各種技術，用列名字或編號作為參數，構造出使用CASE表達式和動態執行能力的各種方案。
|
