您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server TOP 子句參數化最佳實踐：變數與 sp_executesql 結合

編輯：關於SqlServer

問題：TOP 不支援直接變數 (舊版本限制)

SQL Server 2005 之前：`TOP` 只接受常數 (`TOP 10`)，變數需動態 SQL 拼接 (`'SELECT TOP ' + @N + ' ...'`)，導致計畫無法重用、注入風險、可讀性差。

現代標準寫法 (SQL Server 2005+ 全版本支援)

關鍵：括號包裹變數 `TOP (@Variable)`


DECLARE @TopCount INT = 100;
SELECT TOP (@TopCount) * 
FROM AdventureWorks.HumanResources.Employee
ORDER BY HireDate DESC;

此寫法：**計畫可重用**、**參數嗅探正常**、**防注入**、**可讀性高**。

進階：搭配動態 WHERE (LIKE/IN) 使用 sp_executesql

當查詢條件結構動態 (如選填欄位、動態排序) 時，`sp_executesql` + 參數化是黃金組合。


DECLARE @TopCount     INT = 50;
DECLARE @TitlePattern NVARCHAR(100) = N'%Manager%'; -- 參數化 LIKE 模式
DECLARE @Gender       NCHAR(1) = N'F';              -- 參數化等值
DECLARE @Sql          NVARCHAR(MAX);

SET @Sql = N'
SELECT TOP (@TopCountPar) 
    BusinessEntityID, JobTitle, HireDate, Gender
FROM HumanResources.Employee
WHERE JobTitle LIKE @TitlePar
  AND Gender = @GenderPar
ORDER BY HireDate DESC;';

-- 執行：參數定義 + 賦值分離，計畫緩存鍵包含參數化查詢文本
EXEC sp_executesql 
    @Sql,
    N'@TopCountPar INT, @TitlePar NVARCHAR(100), @GenderPar NCHAR(1)',
    @TopCountPar = @TopCount,
    @TitlePar    = @TitlePattern,
    @GenderPar   = @Gender;

為什麼不用 EXEC (@SqlString) ?

維度	EXEC (@String)	sp_executesql
計畫重用	否 (每次字串不同產生新計畫)	是 (參數化文本相同即重用)
參數化	手動拼接字串 (注入風險、型別轉換錯誤)	強型別參數傳遞 (安全、正確)
效能	編譯開銷大、計畫緩存膨脹	編譯一次重用多次
變數作用域	需在字串內宣告/賦值	外部變數直接映射

動態 TOP + 動態 ORDER BY 的安全模式

`ORDER BY` 不支援參數化，需白名單驗證後拼接。


CREATE PROCEDURE dbo.GetTopEmployees
    @TopCount   INT = 20,
    @SortColumn SYSNAME = 'HireDate', -- 白名單驗證
    @SortOrder  VARCHAR(4) = 'DESC'   -- 'ASC'/'DESC'
AS
BEGIN
    -- 白名單驗證
    IF @SortColumn NOT IN (N'HireDate', N'BusinessEntityID', N'JobTitle', N'Gender')
        THROW 50001, N'Invalid sort column.', 1;
    IF @SortOrder NOT IN ('ASC', 'DESC')
        THROW 50002, N'Invalid sort order.', 1;

    DECLARE @Sql NVARCHAR(MAX) = N'
        SELECT TOP (@TopCountPar) BusinessEntityID, JobTitle, HireDate, Gender
        FROM HumanResources.Employee
        ORDER BY ' + QUOTENAME(@SortColumn) + N' ' + @SortOrder + N';';

    EXEC sp_executesql @Sql, N'@TopCountPar INT', @TopCountPar = @TopCount;
END

效能微調提示

大 TOP 值：`TOP (1000000)` 等同無 TOP，優化器可能選擇全掃描。若需大量資料，考慮 `OFFSET-FETCH` 或 Keyset 分頁。
TOP 與 ORDER BY：`TOP` **必須** 搭配 `ORDER BY` 才有確定語義，否則結果不確定。確保 `ORDER BY` 欄位有索引支援 (避免 Top-N Sort 昂貴操作)。
TOP 與 TIES：`SELECT TOP (10) WITH TIES ... ORDER BY Score DESC` 會包含並列第 10 名的所有行，結果行數可能 > 10。