萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

以欄位名稱為參數實現動態排序的完整演進

二、用欄位名稱作為參數

另一種選擇是讓查詢接收欄位名稱作為參數。Listing 2 展示修改後的 GetSortedShippers 存儲過程,CASE 表達式根據參數決定 ORDER BY 使用的欄位值。注意:ORDER BY 中的表達式未出現在 SELECT 清單中。ANSI SQL-92 不允許,但 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' 正常;但傳入 '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,導致字串被嘗試轉換為整數而失效。

Listing 3:第二次嘗試 - 將 ShipperID 轉為 varchar

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」。

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

此法將數值轉為固定長度字串 (含符號),使字串排序等同數值排序。

Listing 6:Richard Romley 的巧妙做法 - 多個獨立 CASE

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,各自返回對應類型,避免類型衝突。但需注意:只有指定欄位無需計算時,索引才能優化排序。

copyright © 萬盛學電腦網 all rights reserved