我之前在处理SQL Server存储过程调用链的时候，也碰到过和你一模一样的难题——嵌套INSERT INTO...EXEC受限，转成表值函数又没法带状态码输出。经过几次踩坑，整理了几个可行的方案，你可以根据业务场景挑最合适的：

方案1：让表值函数返回包含状态码的结果集 #

这是最直接的思路：把状态码作为额外字段加到函数返回的表结构里，不管有没有业务数据，都返回包含状态码的行。调用方先读取状态码，再决定如何处理后续数据。

示例函数： #

CREATE FUNCTION dbo.GetTargetData(@InputParam INT)
RETURNS @Result TABLE (
    StatusCode INT,          -- 0=成功，1=输入缺失
    DataCol1 VARCHAR(50),    -- 业务字段1
    DataCol2 INT             -- 业务字段2
)
AS
BEGIN
    -- 先检查输入合法性
    IF @InputParam IS NULL
    BEGIN
        INSERT INTO @Result (StatusCode, DataCol1, DataCol2)
        VALUES (1, NULL, NULL);
        RETURN;
    END

    -- 正常业务逻辑，返回数据+成功状态
    INSERT INTO @Result (StatusCode, DataCol1, DataCol2)
    SELECT 0, SourceCol1, SourceCol2
    FROM dbo.SourceTable
    WHERE FilterCol = @InputParam;

    RETURN;
END

调用方式（避免重复执行函数）： #

为了避免两次调用函数导致逻辑重复执行，建议先把结果存入临时表/表变量：

DECLARE @TempResult TABLE (
    StatusCode INT,
    DataCol1 VARCHAR(50),
    DataCol2 INT
);

-- 一次性获取状态+数据
INSERT INTO @TempResult
SELECT * FROM dbo.GetTargetData(123);

DECLARE @Status INT = (SELECT TOP 1 StatusCode FROM @TempResult);

IF @Status = 0
BEGIN
    -- 处理正常数据，插入到目标表
    INSERT INTO dbo.TargetTable (Col1, Col2)
    SELECT DataCol1, DataCol2 FROM @TempResult;
END
ELSE
BEGIN
    -- 处理输入缺失的逻辑
    PRINT '错误：输入参数缺失';
    -- 也可以抛出自定义错误：THROW 50001, '输入数据缺失', 1;
END

优缺点：实现简单，不需要改太多原有逻辑；缺点是如果函数逻辑复杂、数据量大，返回的结果集会包含冗余的状态码字段，但整体影响不大。

方案2：改用存储过程+临时表传递结果+状态码 #

既然嵌套INSERT INTO...EXEC受限，我们可以用会话级临时表来绕开这个限制——临时表在同一个会话中对所有调用层级可见，底层存储过程可以把数据写入临时表，同时用OUTPUT参数返回状态码。

底层存储过程示例： #

CREATE PROCEDURE dbo.GetDataProc
    @InputParam INT,
    @StatusCode INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @StatusCode = 0;

    -- 检查输入
    IF @InputParam IS NULL
    BEGIN
        SET @StatusCode = 1;
        RETURN;
    END

    -- 如果临时表不存在则创建（也可以由上层过程提前创建，更可控）
    IF OBJECT_ID('tempdb..#ProcedureResult') IS NULL
    BEGIN
        CREATE TABLE #ProcedureResult (
            DataCol1 VARCHAR(50),
            DataCol2 INT
        );
    END

    -- 写入业务数据到临时表
    INSERT INTO #ProcedureResult
    SELECT SourceCol1, SourceCol2
    FROM dbo.SourceTable
    WHERE FilterCol = @InputParam;
END

上层调用示例（比如存储过程B）： #

CREATE PROCEDURE dbo.ProcedureB
    @InputParam INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Status INT;
    -- 提前创建临时表，避免底层过程创建时的权限/存在性问题
    CREATE TABLE #ProcedureResult (
        DataCol1 VARCHAR(50),
        DataCol2 INT
    );

    -- 调用底层存储过程，获取状态码
    EXEC dbo.GetDataProc @InputParam, @Status OUTPUT;

    IF @Status = 0
    BEGIN
        -- 把数据返回给更上层的A，或者插入到其他表
        SELECT * FROM #ProcedureResult;
    END
    ELSE
    BEGIN
        -- 返回错误状态，或者空结果集+状态标识
        SELECT 1 AS StatusCode;
    END
END

优缺点：适合复杂业务逻辑，避免了函数重复执行的性能问题；缺点是需要管理临时表的生命周期，跨过程调用时要注意临时表的命名冲突。

方案3：使用自定义表类型（UDT）封装复合结果 #

如果需要在多个地方复用“状态码+业务数据”的结构，可以创建自定义表类型，让函数返回这个类型，结构更清晰。

步骤1：创建自定义表类型 #

CREATE TYPE dbo.DataWithStatus AS TABLE (
    StatusCode INT,
    DataCol1 VARCHAR(50),
    DataCol2 INT
);

步骤2：返回自定义类型的函数 #

CREATE FUNCTION dbo.GetDataWithUDT(@InputParam INT)
RETURNS dbo.DataWithStatus
AS
BEGIN
    DECLARE @Result dbo.DataWithStatus;

    IF @InputParam IS NULL
    BEGIN
        INSERT INTO @Result (StatusCode, DataCol1, DataCol2)
        VALUES (1, NULL, NULL);
        RETURN @Result;
    END

    INSERT INTO @Result (StatusCode, DataCol1, DataCol2)
    SELECT 0, SourceCol1, SourceCol2
    FROM dbo.SourceTable
    WHERE FilterCol = @InputParam;

    RETURN @Result;
END

调用方式 #

DECLARE @Result dbo.DataWithStatus;
INSERT INTO @Result SELECT * FROM dbo.GetDataWithUDT(123);

DECLARE @Status INT = (SELECT TOP 1 StatusCode FROM @Result);

IF @Status = 0
BEGIN
    INSERT INTO dbo.TargetTable (Col1, Col2)
    SELECT DataCol1, DataCol2 FROM @Result;
END

优缺点：结构复用性强，代码更整洁；缺点是需要额外维护自定义类型，对团队成员的SQL Server特性熟悉度有要求。

内容的提问来源于stack exchange，提问作者FDavidov