在公共新聞組中一個經常出現的問題是怎樣才能根據傳遞給存儲過程的參數返回一個排序的輸出?在一些高水平專家的幫助之下我整理出了這個問題的幾種解決方案
一用IFELSE執行預先編寫好的查詢
對於大多數人來說首先想到的做法也許是通過IFELSE語句執行幾個預先編寫好的查詢中的一個例如假設要從Northwind數據庫查詢得到一個貨主(Shipper)的排序列表發出調用的代碼以存儲過程參數的形式指定一個列存儲過程根據這個列排序輸出結果Listing 顯示了這種存儲過程的一個可能的實現(GetSortedShippers存儲過程)
【Listing : 用IFELSE執行多個預先編寫好的查詢中的一個】
CREATE PROC GetSortedShippers
@OrdSeq AS int
AS
IF @OrdSeq =
SELECT * FROM Shippers ORDER BY ShipperID
ELSE IF @OrdSeq =
SELECT * FROM Shippers ORDER BY CompanyName
ELSE IF @OrdSeq =
SELECT * FROM Shippers ORDER BY Phone
這種方法的優點是代碼很簡單很容易理解SQL Server的查詢優化器能夠為每一個SELECT查詢創建一個查詢優化計劃確保代碼具有最優的性能這種方法最主要的缺點是如果查詢的要求發生了改變你必須修改多個獨立的SELECT查詢(在這裡是三個)
二用列名字作為參數
另外一個選擇是讓查詢以參數的形式接收一個列名字Listing 顯示了修改後的GetSortedShippers存儲過程CASE表達式根據接收到的參數確定SQL Server在ORDER BY子句中使用哪一個列值注意ORDER BY子句中的表達式並未在SELECT清單中出現在ANSI SQL標准中ORDER BY子句中不允許出現沒有在SELECT清單中指定的表達式但ANSI SQL標准允許SQL Server一直允許這種用法
【Listing 用列名字作為參數第一次嘗試】
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 Level State Procedure GetSortedShippers Line
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 顯示了修改後的GetSortedShippers存儲過程
【Listing 用列名字作為參數第二次嘗試】
ALTER PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColName
WHEN ShipperID
THEN CAST(ShipperID AS varchar())
WHEN CompanyName
THEN CompanyName
WHEN Phone
THEN Phone
ELSE NULL
END
現在假設我們再把三個列名字中的任意一個作為參數調用存儲過程輸出結果看起來正確看起來就象指定的列正確地為查詢輸出提供了排序標准但這個表只有三個貨主它們的ID分別是假設我們把更多的貨主加入到表如Listing 所示(ShipperID列有IDENTITY屬性SQL Server自動為該列生成值)
【Listing 向Shippers表插入一些記錄】
INSERT INTO Shippers VALUES(Shipper () )
INSERT INTO Shippers VALUES(Shipper () )
INSERT INTO Shippers VALUES(Shipper () )
INSERT INTO Shippers VALUES(Shipper () )
INSERT INTO Shippers VALUES(Shipper () )
INSERT INTO Shippers VALUES(Shipper () )
INSERT INTO Shippers VALUES(Shipper () )
現在調用存儲過程指定ShipperID作為排序列
EXEC GetSortedShippers ShipperID
表一顯示了存儲過程的輸出ShipperID等於的記錄位置錯誤因為這個存儲過程的排序輸出是字符排序而不是整數排序按照字符排序時排列在的前面因為的開始字符是
表一記錄排序錯誤的查詢結果
ShipperID CompanyName Phone
Speedy Express ()
Shipper ()
United Package ()
Federal Shipping ()
Shipper ()
Shipper ()
Shipper ()
Shipper ()
Shipper ()
Shipper ()
為了解決這個問題我們可以用前置的補足ShipperID值使得ShipperID值都有同樣的長度按照這種方法基於字符的排序具有和整數排序同樣的輸出結果修改後的存儲過程如Listing 所示十個被置於ShipperID的絕對值之前而在結果中代碼只是使用最右邊的個字符SIGN函數確定在正數的前面加上加號(+)前綴還是在負數的前面加上負號()前綴按照這種方法輸出結果總是有個字符包含一個+或字符前導的字符以及ShipperID的絕對值
【Listing 用列名字作為參數第三次嘗試】
ALTER PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColName
WHEN ShipperID THEN CASE SIGN(ShipperID)
WHEN THEN
WHEN THEN +
WHEN THEN +
ELSE NULL
END + RIGHT(REPLICATE( ) + CAST(ABS(ShipperID) AS varchar()) )
WHEN CompanyName THEN CompanyName
WHEN Phone THEN Phone
ELSE NULL
END
如果ShipperID的值都是正數加上符號前綴就沒有必要但為了讓方案適用於盡可能多的范圍本例加上了符號前綴排序時在+的前面所以它可以用於正負數混雜排序的情況
現在如果我們用任意三個列名字之一作為參數調用存儲過程存儲過程都能夠正確地返回結果Richard Romley提出了一種巧妙的處理方法如Listing 所示它不再要求我們搞清楚可能涉及的列數據類型這種方法把ORDER BY子句分成三個獨立的CASE表達式每一個表達式處理一個不同的列避免了由於CASE只返回一種特定數據類型的能力而導致的問題
【Listing 用列名字作為參數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 顯示了修改後的GetSortedShippers存儲過程
【Listing 用列號作為參數】
ALTER PROC GetSortedShippers
@ColNumber AS int
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColNumber
WHEN THEN CASE SIGN(ShipperID)
WHEN THEN
WHEN THEN +
WHEN THEN +
ELSE NULL
END + RIGHT(REPLICATE( ) + CAST(ABS(ShipperID) AS varchar()) )
WHEN THEN CompanyName
WHEN THEN Phone
ELSE NULL
END
當然在這裡你也可以使用Richard的方法避免ORDER BY子句中列數據類型帶來的問題如果要根據ShipperID排序輸出你可以按照下面的方式調用修改後的GetSortedShippers存儲過程 EXEC GetSortedShippers
四動態執行
使用動態執行技術我們能夠更輕松地編寫出GetSortedShippers存儲過程使用這種方法我們只需動態地構造出SELECT語句然後用EXEC()命令執行這個SELECT語句假設傳遞給存儲過程的參數是列的名字存儲過程可以大大縮短
ALTER PROC GetSortedShippers
@ColName AS sysname
AS
EXEC(SELECT * FROM Shippers ORDER BY + @ColName)
在SQL Server 和中你可以用系統存儲過程sp_ExecuteSQL替代Exec()命令BOL說明了使用sp_ExecuteSQL比使用Exec()命令更有利的地方一般地如果滿足以下三個條件你能夠在不授予存儲過程所涉及對象權限的情況下授予執行存儲過程的權限
首先只使用Data Manipulation Language(DML)語言(即SELECTINSERTUPDATEDELETE);其次所有被引用的對象都有與存儲過程同樣的所有者;第三沒有使用動態命令
上面的存儲過程不能滿足第三個條件在這種情況下你必須為所有需要使用存儲過程的用戶和組顯式地授予Shippers表的SELECT權限如果這一點可以接受的話一切不存在問題類似地你可以修改存儲過程使它接受一個列號參數如Listing 所示
【Listing 用列號作為參數動態執行(代碼較長的方法)】
ALTER PROC GetSortedShippers
@ColNumber AS int
AS
DECLARE @cmd AS varchar()
SET @cmd = SELECT * FROM Shippers ORDER BY +
CASE @ColNumber
WHEN THEN ShipperID
WHEN THEN CompanyName
WHEN THEN Phone
ELSE NULL
END
EXEC(@cmd)
注意當你使用了函數時你應該在一個變量而不是EXEC()命令內構造SELECT語句此時CASE表達式動態地確定使用哪一個列還有一種更簡短的格式TSQL允許在ORDER BY子句中指定SELECT清單中列的位置如Listing 所示這種格式遵從了SQL標准但ANSI SQL標准不支持這種格式所以最好不要使用這種格式
【Listing 列號作為參數動態執行(代碼較短的方法)】
ALTER PROC GetSortedShippers
@ColNumber AS int
AS
DECLARE @cmd AS varchar()
SET @cmd = SELECT * FROM Shippers ORDER BY + CAST(@ColNumber AS varchar())
EXEC(@cmd)
五用戶定義函數
如果你使用的是SQL Server 想要編寫一個用戶定義的函數(UDF)這個用戶定義函數接受列的名字或編號為參數返回排序的結果集Listing 顯示了大多數程序員當成第一選擇的方法
【Listing 列名字作為參數使用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 THEN
WHEN THEN +
WHEN THEN +
ELSE NULL
END + RIGHT(REPLICATE( ) + CAST(ABS(ShipperID) AS
varchar()) )
WHEN CompanyName THEN CompanyName
WHEN Phone THEN Phone
ELSE NULL
END
但是SQL Server不接受這個函數它將返回如下錯誤信息
Server: Msg Level State Procedure ufn_GetSortedShippers Line
The ORDER BY clause is invalid in views inline functions and
subqueries unless TOP is also specified
注意錯誤信息中的unlessSQL Server 不允許在視圖嵌入式UDF子查詢中出現ORDER BY子句因為它們都應該返回一個表表不能指定行的次序然而如果使用了TOP關鍵詞ORDER BY子句將幫助確定查詢所返回的行因此如果指定了TOP你還可以同時指定ORDER BY由於在帶有TOP的UDF中允許使用ORDER BY子句你可以使用一個技巧把SELECT *替換成SELECT TOP PERCENT *這樣你就能夠成功地構造出一個接受列名字或編號為參數返回排序結果的函數
新構造的函數可以按照如下方式調用
SELECT * FROM ufn_GetSortedShippers(ShipperID)
現在你已經了解了幾種用參數確定查詢輸出中記錄次序的方法在編寫那些允許用戶指定查詢結果排序標准的列的應用程序時你可以使用本文介紹的各種技術用列名字或編號作為參數構造出使用CASE表達式和動態執行能力的各種方案
From:http://tw.wingwit.com/Article/program/SQL/201311/16169.html
