SQL 動態排序實戰：CASE 表達式在 ORDER BY 中的進階應用

打破靜態排序：CASE 表達式賦予 ORDER BY 動態靈活性

固定 ORDER BY Column 無法滿足「使用者自訂排序欄位/方向」的需求。本文示範如何在 ORDER BY 子句中嵌入 CASE 表達式，配合參數實現完全動態的多欄位、多方向排序，並封裝為預存程序供應用層呼叫。

基礎語法回顧

-- 簡單 CASE
CASE @SortKey
    WHEN 1 THEN ColumnA
    WHEN 2 THEN ColumnB
    ELSE ColumnC
END

-- 搜尋 CASE (支援複雜條件)
CASE
    WHEN @SortKey = 1 AND @Dir = 'ASC'  THEN ColumnA
    WHEN @SortKey = 1 AND @Dir = 'DESC' THEN ColumnA DESC  -- 錯誤！CASE 回傳值不可含 DESC
    ELSE ColumnC
END

關鍵限制：CASE 只能回傳「值」，不能回傳「排序方向」

錯誤寫法：ORDER BY CASE WHEN @Dir='DESC' THEN Col END DESC — 語法錯誤

正確模式：雙層 CASE 或動態 SQL

方案一：雙層 CASE (單方向動態欄位)

CREATE PROCEDURE usp_GetCustomersDynamicSort
    @SortColumn TINYINT = 1  -- 1=CompanyName, 2=ContactName, 3=ContactTitle
AS
SELECT CompanyName, ContactName, ContactTitle
FROM Customers
ORDER BY
    CASE @SortColumn
        WHEN 1 THEN CompanyName
        WHEN 2 THEN ContactName
        ELSE ContactTitle
    END;  -- 僅支援 ASC

方案二：動態 SQL (全動態：欄位+方向)

CREATE PROCEDURE usp_GetCustomersFullDynamicSort
    @SortColumn NVARCHAR(50) = 'CompanyName',  -- 欄位名
    @SortDir    VARCHAR(4)   = 'ASC'           -- ASC/DESC
AS
BEGIN
    SET NOCOUNT ON;
    -- 防注入：白名單驗證
    IF @SortColumn NOT IN ('CompanyName','ContactName','ContactTitle') SET @SortColumn = 'CompanyName';
    IF @SortDir NOT IN ('ASC','DESC') SET @SortDir = 'ASC';

    DECLARE @sql NVARCHAR(MAX) = N'
        SELECT CompanyName, ContactName, ContactTitle
        FROM Customers
        ORDER BY ' + QUOTENAME(@SortColumn) + ' ' + @SortDir + ';';

    EXEC sp_executesql @sql;
END

方案三：計算排序鍵 (單查詢、無動態 SQL、支援多欄位混合方向)

SELECT CompanyName, ContactName, ContactTitle
FROM Customers
ORDER BY
    CASE WHEN @SortColumn=1 AND @SortDir='ASC'  THEN CompanyName END ASC,
    CASE WHEN @SortColumn=1 AND @SortDir='DESC' THEN CompanyName END DESC,
    CASE WHEN @SortColumn=2 AND @SortDir='ASC'  THEN ContactName END ASC,
    CASE WHEN @SortColumn=2 AND @SortDir='DESC' THEN ContactName END DESC,
    CASE WHEN @SortColumn=3 AND @SortDir='ASC'  THEN ContactTitle END ASC,
    CASE WHEN @SortColumn=3 AND @SortDir='DESC' THEN ContactTitle END DESC;

原理：非符合條件的 CASE 回傳 NULL，NULL 排在最前 (ASC) 或最後 (DESC)，不影響結果順序。

效能比較與選型建議

進階技巧：搭配索引提示

動態排序導致計劃難以針對特定索引優化。可考慮：

• 建立覆蓋索引 INCLUDE 所有 SELECT 欄位
• 使用 OPTION (RECOMPILE) 讓參數嗅探生成最優計劃 (低頻呼叫)
• 高頻熱點：拆分為多個固定排序 SP，應用層路由

總結

CASE 在 ORDER BY 中是實現「參數化排序欄位」的標準技巧；需「動態方向」時，多層 CASE 兼顧計劃重用與安全性；極少數完全動態場景才啟用動態 SQL，並嚴格白名單驗證。