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

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

　　一、用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表示式中最高優先級的數據類型決定了表達式返回值的數據類型?！皵祿愋蛢炏燃墶币巹t可以在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'

　　

原文轉自：http://www.anti-gravitydesign.com