二、用列名字作為參數
另外一個選擇是讓查詢以參數的形式接收一個列名字。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。