數據庫查詢結果的動態排序(2)

二、用列名字作為參數

　　另外一個選擇是讓查詢以參數的形式接收一個列名字。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表達式返回恰當的數據類型，而且無需進行數據類型轉換。但應該注意的是，只有當指定的列不需要進行計算時，索引才能夠優化排序操作。

關鍵詞：排序