SQL Server how to call 2 stored procedure with table of data inside a stored procedure to make count of rows in it?

cidc1ykv 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(124)

I have 2 stored procedures, it has different columns from 2 different tables. I want to count the results(no. of rows) of these 2 stored procedures by creating the temporary table But I don't know how to pull this. 1st procedure:

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
[setable_workflowtable] 'CloudTeam'

2nd procedure:

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'

This is what I tried

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

I want my result to be like this

Pending4Approval	Pending4Implementation
10	0

sql-server

来源：https://stackoverflow.com/questions/76689628/how-to-call-2-stored-procedure-with-table-of-data-inside-a-stored-procedure-to-m

1条答案

按热度按时间

yrdbyhpb1#

CREATE PROCEDURE Se_TechAdmin_Dashboard_Count( @username nvarchar(100))
AS
BEGIN 
     CREATE TABLE #TempTable1 ( SecurityExceptionID INT,
                                SecurityExceptionInfoID NVARCHAR(100),
                                WorkflowID INT,
                                CatalogueType NVARCHAR(100),
                                CatalogueSubType NVARCHAR(100),
                                EofE NVARCHAR(500),
                                EofEID INT,
                                UserWhoCreated NVARCHAR(250),
                                DateCreated datetime2(7),    
                                TechTeam NVARCHAR(50),
                                WorkflowSystemStatus NVARCHAR(100),
                                NextActionByUser NVARCHAR(100),
                                CurrentStatus NVARCHAR(100),
                                NextActionBy NVARCHAR(100),
                                dummy1 nvarchar(100) ,
                                dummy2 nvarchar(100) ,
                                dummy3 nvarchar(100) ,
                                dummy4 nvarchar(100) )
     INSERT INTO #TempTable1

EXEC [dbo].[setable_workflowtable] @techteam = @username 

     CREATE TABLE #TempTable2 (TaskID int,
                               [SecurityExceptionID] int,       
                               Taskcreatedby nvarchar(250),
                               TaskAssignedto nvarchar (250),
                               [TechTeam] nvarchar(250),
                               [TaskStatus] nchar(10),
                               [TaskCreatedDate] datetime2(7),
                               [TaskAssignedDate] datetime2(7),
                               [TaskExpectedCompletionDate] datetime2(7),
                               [TaskActualCompletedDate] datetime2(7),
                               [DateCreated] datetime2(7),
                               [DateModified] datetime2(7),
                               [TaskComments] nvarchar(1000),
                               [SecurityExceptionInfoID] nvarchar(50),
                               [CatalogueType] nvarchar(100),
                               [CatalogueSubType] nvarchar(100),
                               [EofE] nvarchar(500),
                               [UserWhoCreated] nvarchar (250),
                               [EofEID] int,
                               dummy1 nvarchar(100),
                               dummy2 nvarchar(100),
                               dummy3 nvarchar(100), 
                               dummy4 nvarchar(100))      
     INSERT INTO #TempTable2

EXEC [dbo].[setable_setask] @techteam = @username 

     DECLARE @Count1 INT, @Count2 INT
     SELECT @Count1 = COUNT(*) FROM #TempTable1
     SELECT @Count2 = COUNT(*) FROM #TempTable2
     SELECT @Count1 AS Pending4Approval, @Count2 AS Pending4Implementation
     DROP TABLE #TempTable1
     DROP TABLE #TempTable2
END

EXEC Se_TechAdmin_Dashboard_Count '''CloudTeam'',''SOCTeam'''

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server how to call 2 stored procedure with table of data inside a stored procedure to make count of rows in it?

1条答案

相关问题

热门标签

最新问答