SQL Server 動態 SQL 返回值技巧：sp_executesql 輸出參數

核心語法

DECLARE @TransactorID INT;
SET @TransactorID = 0;
PRINT @TransactorID;  -- 0

-- 關鍵：參數聲明加 OUTPUT，調用時加 OUTPUT
EXEC sp_executesql 
    N'SELECT @TransactorID = 5',          -- 動態 SQL
    N'@TransactorID INT OUTPUT',          -- 參數定義 (注意 OUTPUT)
    @TransactorID OUTPUT;                 -- 變量綁定 (注意 OUTPUT)

PRINT @TransactorID;  -- 5

三個 OUTPUT 缺一不可

1. 參數定義字符串中：@TransactorID INT OUTPUT
2. 調用時變量後：@TransactorID OUTPUT
3. 動態 SQL 內部賦值：SELECT @TransactorID = 5 (隱含輸出)

對比：EXEC() 無法返回值

DECLARE @id INT;
EXEC('SELECT @id = 10');  -- 錯誤：動態作用域變量不共享
PRINT @id;  -- 仍為 NULL/0

EXEC() 创建新會話上下文，變量隔離。sp_executesql 支持參數化輸入輸出，共享調用者上下文。

實用場景：動態構建條件查詢並返回計數

CREATE PROC GetCountByDynamicFilter
    @TableName SYSNAME,
    @Filter NVARCHAR(MAX),
    @RowCount INT OUTPUT
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = 
        N'SELECT @RowCount = COUNT(*) FROM ' + QUOTENAME(@TableName) + 
        N' WHERE ' + @Filter;

    EXEC sp_executesql @SQL,
        N'@RowCount INT OUTPUT',
        @RowCount OUTPUT;
END

-- 調用
DECLARE @cnt INT;
EXEC GetCountByDynamicFilter 'orders', 'status=1 AND amount>100', @cnt OUTPUT;
SELECT @cnt;

安全最佳實踐

• 表名/列名用 QUOTENAME() 防注入
• 用戶輸入值**必須**作為參數傳遞，不可拼接
• 限制動態 SQL 權限：僅授予 EXECUTE 權限，不給 DDL