如何在存储过程中调用两个存储过程并通过临时表统计结果行数

我有两个列结构不同的存储过程，分别从不同表取数，希望通过临时表统计它们执行结果的行数，最终输出指定格式的统计表格。

两个存储过程详情 #

第一个存储过程：setable_workflowtable #

CREATE PROCEDURE [dbo].[setable_workflowtable]    
    @techteam nvarchar(100) = NULL    
AS    
BEGIN    
    SELECT
        s.SecurityExceptionID, s.SecurityExceptionInfoID,
        w.WorkflowID ,s.CatalogueType, 
        s.CatalogueSubType, s.EofE,
        s.EofEID, s.UserWhoCreated, s.DateCreated,  
        w.TechTeam, w.WorkflowSystemStatus, 
        w.NextActionByUser, w.CurrentStatus, w.NextActionBy, 
        NULL AS 'dummy1', NULL AS 'dummy2', NULL AS 'dummy3', 
        NULL AS 'dummy4'    
    FROM
        WorkflowTable w    
    INNER JOIN
        SETable s ON w.SecurityExceptionID = s.SecurityExceptionID    
    WHERE 
        w.currentstatus = 'Started' 
        AND w.WorkflowSystemStatus != 'SecurityTeamApproved' 
        AND w.NextActionBy = 'TechUser' 
        AND w.TechTeam = @techteam 
    ORDER BY  
        datecreated DESC
END

-- 执行示例
EXEC [setable_workflowtable] 'CloudTeam'

第二个存储过程：setable_setask #

CREATE PROCEDURE [dbo].[setable_setask]    
    @techteam nvarchar(100) = NULL    
AS    
BEGIN    
    IF (@techteam IS NULL OR @techteam = 'All')  
    BEGIN
        SELECT
            setask.*, 
            setable.SecurityExceptionInfoID, setable.CatalogueType, 
            setable.CatalogueSubType, setable.EofE, 
            setable.UserWhoCreated, setable.EofEID,  
            NULL AS 'dummy1', NULL AS 'dummy2', 
            NULL AS 'dummy3', NULL AS 'dummy4'    
        FROM
            SETask setask  
        INNER JOIN
            SETable setable ON setask.SecurityExceptionID = setable.SecurityExceptionID    
    END
    ELSE
    BEGIN
        SELECT
            setask.*, 
            setable.SecurityExceptionInfoID, setable.CatalogueType, 
            setable.CatalogueSubType, setable.EofE, 
            setable.UserWhoCreated, setable.EofEID,  
            NULL AS 'dummy1', NULL AS 'dummy2', 
            NULL AS 'dummy3', NULL AS 'dummy4'    
        FROM
            SETask setask  
        INNER JOIN
            SETable setable ON setask.SecurityExceptionID = setable.SecurityExceptionID    
        WHERE 
            setask.TechTeam = @techteam  
        ORDER BY
            datecreated DESC
    END
END

-- 执行示例
EXEC [setable_setask] 'Cloudteam'

尝试过的方法（未得到期望格式） #

CREATE PROCEDURE count
    @userTeam NVARCHAR(100),
    @Count1 INT OUTPUT,
    @Count2 INT OUTPUT
AS
BEGIN
    EXEC [dbo].[setable_workflowtable] @techteam = @userTeam;
    SELECT @Count1 = @@ROWCOUNT;

    EXEC [dbo].[SecondProcedure] @techteam = @userteam;
    SELECT @Count2 = @@ROWCOUNT;
END

期望输出格式 #

由于两个存储过程返回列结构不同，需通过临时表接收结果后统计行数，再组装成目标格式：

方法一：手动定义临时表（推荐，兼容性强） #

CREATE PROCEDURE GetPendingCounts
    @techteam NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    -- 创建临时表接收第一个存储过程结果（列类型需与存储过程返回值匹配）
    CREATE TABLE #TempWorkflow (
        SecurityExceptionID INT,
        SecurityExceptionInfoID INT,
        WorkflowID INT,
        CatalogueType NVARCHAR(50),
        CatalogueSubType NVARCHAR(50),
        EofE NVARCHAR(50),
        EofEID INT,
        UserWhoCreated NVARCHAR(100),
        DateCreated DATETIME,
        TechTeam NVARCHAR(100),
        WorkflowSystemStatus NVARCHAR(50),
        NextActionByUser NVARCHAR(100),
        CurrentStatus NVARCHAR(50),
        NextActionBy NVARCHAR(50),
        dummy1 NVARCHAR(50),
        dummy2 NVARCHAR(50),
        dummy3 NVARCHAR(50),
        dummy4 NVARCHAR(50)
    );

    -- 插入结果并统计行数
    INSERT INTO #TempWorkflow
    EXEC [dbo].[setable_workflowtable] @techteam = @techteam;
    DECLARE @Pending4Approval INT = @@ROWCOUNT;

    -- 创建临时表接收第二个存储过程结果（列类型需与存储过程返回值匹配）
    CREATE TABLE #TempSETask (
        -- 先列出SETask表所有列，再添加关联列和dummy列
        SETaskID INT,
        SecurityExceptionID INT,
        TechTeam NVARCHAR(100),
        -- 补充SETask其他列...
        SecurityExceptionInfoID INT,
        CatalogueType NVARCHAR(50),
        CatalogueSubType NVARCHAR(50),
        EofE NVARCHAR(50),
        UserWhoCreated NVARCHAR(100),
        EofEID INT,
        dummy1 NVARCHAR(50),
        dummy2 NVARCHAR(50),
        dummy3 NVARCHAR(50),
        dummy4 NVARCHAR(50)
    );

    -- 插入结果并统计行数
    INSERT INTO #TempSETask
    EXEC [dbo].[setable_setask] @techteam = @techteam;
    DECLARE @Pending4Implementation INT = @@ROWCOUNT;

    -- 输出目标格式结果
    SELECT 
        @Pending4Approval AS Pending4Approval,
        @Pending4Implementation AS Pending4Implementation;

    -- 清理临时表
    DROP TABLE #TempWorkflow;
    DROP TABLE #TempSETask;
END

方法二：自动生成临时表（简化操作，需开启配置） #

如果不想手动定义列，可通过OPENROWSET自动创建临时表：

CREATE PROCEDURE GetPendingCounts
    @techteam NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    -- 自动创建临时表并插入第一个存储过程结果
    SELECT * INTO #TempWorkflow
    FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;',
                    'EXEC [YourDatabaseName].[dbo].[setable_workflowtable] @techteam = ''' + @techteam + '''');
    DECLARE @Pending4Approval INT = (SELECT COUNT(*) FROM #TempWorkflow);

    -- 自动创建临时表并插入第二个存储过程结果
    SELECT * INTO #TempSETask
    FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;',
                    'EXEC [YourDatabaseName].[dbo].[setable_setask] @techteam = ''' + @techteam + '''');
    DECLARE @Pending4Implementation INT = (SELECT COUNT(*) FROM #TempSETask);

    -- 输出目标格式结果
    SELECT 
        @Pending4Approval AS Pending4Approval,
        @Pending4Implementation AS Pending4Implementation;

    -- 清理临时表
    DROP TABLE #TempWorkflow;
    DROP TABLE #TempSETask;
END

注意：使用此方法需先开启Ad Hoc Distributed Queries配置：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

原方法失败原因 #

直接用EXEC后取@@ROWCOUNT，若存储过程内部开启了SET NOCOUNT ON，或执行过程中其他操作影响行计数，会导致@@ROWCOUNT无法准确获取结果行数。通过临时表插入的方式，@@ROWCOUNT或COUNT(*)能稳定返回实际行数。

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