存儲過程互相調用並獲取返回值的三種實用方法

在 SQL Server 中，一個存儲過程呼叫另一個並取得回傳值，常見三種模式：OUTPUT 參數、暫存表、動態 SQL + sp_executesql。各有適用場景與限制，本文逐一解析。

方法一：OUTPUT 參數（最標準、效能最佳）

-- 被呼叫端
CREATE PROCEDURE Production.usp_GetList
    @product VARCHAR(40),
    @maxprice MONEY,
    @compareprice MONEY OUTPUT,
    @listprice MONEY OUTPUT
AS
BEGIN
    SELECT p.Name AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product p
    JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.Name LIKE @product AND p.ListPrice < @maxprice;

    SET @listprice = (SELECT MAX(p.ListPrice) FROM ... WHERE ...);
    SET @compareprice = @maxprice;
END;
GO

-- 呼叫端
CREATE PROCEDURE Test
AS
BEGIN
    DECLARE @compareprice MONEY, @cost MONEY;
    EXEC Production.usp_GetList '%Bikes%', 700,
         @compareprice OUTPUT, @cost OUTPUT;

    IF @cost <= @compareprice
        PRINT '可購買...';
    ELSE
        PRINT '價格超過...';
END;

✅ 優點：型別安全、計畫可重用、無額外 I/O。
⚠️ 限制：只能回傳純量值，無法直接回傳結果集。

方法二：暫存表（適合回傳結果集）

CREATE PROCEDURE GetUserName
AS
BEGIN
    SELECT 'UserName';
END;

CREATE TABLE #tempTable (userName NVARCHAR(50));
INSERT INTO #tempTable (userName) EXEC GetUserName;
SELECT * FROM #tempTable;
DROP TABLE #tempTable;

✅ 優點：可接收任意結果集結構。
⚠️ 重大限制：不可巢狀。若 Proc A → INSERT #t EXEC B，而 B 內又 INSERT #t EXEC C，會報錯 INSERT EXEC 語句不能嵌套。

方法三：動態 SQL + sp_executesql（靈活但需防注入）

DECLARE @rsql VARCHAR(250), @csql VARCHAR(300), @rc NVARCHAR(500);
DECLARE @cstucount INT, @ccount INT;

SET @rsql = '(SELECT Classroom_id FROM EA_RoomTime WHERE zc=... ) AND ClassroomType=''1''';
SET @csql = 'SELECT @a=SUM(teststucount), @b=SUM(classcount) FROM EA_ClassRoom WHERE classroom_id IN ';
SET @rc = @csql + @rsql;

EXEC sp_executesql @rc,
    N'@a INT OUTPUT, @b INT OUTPUT',
    @cstucount OUTPUT, @ccount OUTPUT;

✅ 優點：可在執行期組裝任意邏輯、支援 OUTPUT 參數。
⚠️ 風險：動態 SQL 難除錯、易引入 SQL 注入，**嚴禁拼接使用者輸入**，務必參數化。

選擇建議

掌握三種模式並依場景選擇，能寫出高效、可維護的模組化存儲過程。