另一種選擇是讓查詢接收欄位名稱作為參數。Listing 2 展示修改後的 GetSortedShippers 存儲過程,CASE 表達式根據參數決定 ORDER BY 使用的欄位值。注意:ORDER BY 中的表達式未出現在 SELECT 清單中。ANSI SQL-92 不允許,但 SQL Server 一直支援。
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' 正常;但傳入 '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.
原因在於「資料類型優先級」規則:CASE 表達式返回類型由最高優先級的類型決定。int 優先級高於 nvarchar,導致字串被嘗試轉換為整數而失效。
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
雖然三個參數都能執行,但新增資料後發現 ShipperID=10 排在 2 前面,因為字串排序導致「10」小於「2」。
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
此法將數值轉為固定長度字串 (含符號),使字串排序等同數值排序。
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
此法將 ORDER BY 拆為三個獨立 CASE,各自返回對應類型,避免類型衝突。但需注意:只有指定欄位無需計算時,索引才能優化排序。