SQL Server 儲存程序 OUTPUT 參數取不到值？動態 SQL 執行順序與參數綁定陷阱

在 SQL Server 儲存程序中使用 sp_executesql 執行動態 SQL 並嘗試取回 OUTPUT 參數值時，若同一個程序中有多個 sp_executesql 呼叫，且參數宣告不一致，會導致後續呼叫覆蓋或參數未正確綁定，造成 Cmd.Parameters("@TotalCount").value 取不到值。

問題程式碼分析

-- 第一次呼叫：計算總筆數
SET @Exec_sql = 'SET @TotalCount = (SELECT COUNT(1) FROM (' + @Sql + ') AS a)'
EXEC sp_executesql @Exec_sql2, N'@TotalCount INT OUTPUT', @TotalCount OUTPUT -- ❌ 變數名錯誤 @Exec_sql2

-- 第二次呼叫：分頁查詢
EXEC sp_executesql @Exec_sql, N'@CurrentPage INT, @PageSize INT', @CurrentPage, @PageSize

錯誤點：@Exec_sql2 未定義（應為 @Exec_sql），且兩次 sp_executesql 共用同一個 @TotalCount 變數，但第二次呼叫未包含該參數宣告，導致輸出值遺失。

修正方案

1. 合併為單一動態 SQL 批次：在同一個 sp_executesql 中完成總筆數計算與分頁查詢，確保 OUTPUT 參數正確回傳。
2. 或分離變數：第一次呼叫用 @TotalCount，第二次呼叫用不同變數名或不輸出。

修正後範例

ALTER PROC [dbo].[sp_pages] 
    @Sql NVARCHAR(MAX),
    @Order VARCHAR(4000),
    @CurrentPage INT,
    @PageSize INT,
    @TotalCount INT OUTPUT
AS
BEGIN
    DECLARE @Exec_sql NVARCHAR(MAX);
    SET @Exec_sql = N'
        DECLARE @TotalCount INT;
        SELECT @TotalCount = COUNT(1) FROM (' + @Sql + N') AS a;
        
        -- 分頁查詢邏輯
        ' + CASE WHEN @CurrentPage = 1 THEN N'
            WITH CTE AS ( ' + @Sql + N' )
            SELECT TOP(@PageSize) *, ROW_NUMBER() OVER(' + @Order + N') AS r
            FROM CTE ORDER BY r
        ' ELSE N'
            WITH CTE AS (
                SELECT *, ROW_NUMBER() OVER(' + @Order + N') AS r
                FROM (' + @Sql + N') AS a
            )
            SELECT * FROM CTE 
            WHERE r BETWEEN (@CurrentPage-1)*@PageSize+1 AND @CurrentPage*@PageSize
            ORDER BY r
        ' END + N'
        SELECT @TotalCount AS TotalCount; -- 最後輸出總筆數
    ';
    
    EXEC sp_executesql @Exec_sql, 
        N'@CurrentPage INT, @PageSize INT, @TotalCount INT OUTPUT',
        @CurrentPage, @PageSize, @TotalCount OUTPUT;
END

調試建議

• 使用 PRINT @Exec_sql 輸出動態 SQL 檢查語法。
• 在 SSMS 中逐步執行儲存程序，觀察參數值變化。
• 避免在單一程序中多次 sp_executesql 操作同一 OUTPUT 參數，極易出錯。