SQL Server 存儲過程中動態使用 TOP 變量的三種寫法與效能比較

問題背景

存儲過程需根據參數動態決定 TOP N 的行數，但 T-SQL 不支援直接 TOP @variable（SQL 2005 之前），需繞過語法限制。

方法一：動態 SQL + sp_executesql（舊版通用）

ALTER PROC getWorkPlan2
    @intCounter INT,
    @lngUserID  INT
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT TOP (@cnt) lngWorkID, strWorkName, strExecHumanName, strBeginDate
                 FROM worklist
                 WHERE lngExecHumanID = @uid
                 ORDER BY lngWorkID DESC';
    EXEC sp_executesql @sql,
                       N'@cnt INT, @uid INT',
                       @cnt = @intCounter,
                       @uid = @lngUserID;
END;

優點：參數化，防注入，計畫可重用。
缺點：動態 SQL 調試稍繁瑣。

方法二：SET ROWCOUNT（SQL 2005/2008/2012 相容，但已棄用）

ALTER PROC getWorkPlan2
    @intCounter INT,
    @lngUserID  INT
AS
BEGIN
    SET ROWCOUNT @intCounter;
    SELECT lngWorkID, strWorkName, strExecHumanName, strBeginDate
    FROM worklist
    WHERE lngExecHumanID = @lngUserID
    ORDER BY lngWorkID DESC;
    SET ROWCOUNT 0;  -- 重置，影響後續陳述式
END;

注意：SET ROWCOUNT 不影響 INSERT/UPDATE/DELETE 的 TOP 子句（未來版本移除），且會影響該會話後續所有查詢，務必在結束時重置為 0。

方法三：原生 TOP (@variable)（SQL Server 2012+ 推薦）

-- 僅 SQL 2012 及以上支援
ALTER PROC getWorkPlan2
    @intCounter INT,
    @lngUserID  INT
AS
BEGIN
    SELECT TOP (@intCounter) lngWorkID, strWorkName, strExecHumanName, strBeginDate
    FROM worklist
    WHERE lngExecHumanID = @lngUserID
    ORDER BY lngWorkID DESC;
END;

優點：語法簡潔、計畫穩定、無副作用。
限制：SQL 2005/2008/R2 不支援。

效能與最佳實踐建議

常見錯誤避坑

• 字串拼接未參數化：'SELECT TOP ' + CAST(@intCounter AS VARCHAR) + ' ...' → 計畫無法重用、注入風險。
• SET ROWCOUNT 未重置導致後續查詢意外截斷。
• 動態 SQL 中 ORDER BY 缺失導致 TOP 結果不確定。