Executing multiple sub stored procedures in a master stored procedure

Question

I am using multiple stored procedures (return type tabular only 1 row in result) in a master stored procedure:

CREATE PROCEDURE [dbo].[MAster_sp]
    (@param INT) 
AS
BEGIN
    EXEC [dbo].[sp1] @param 
    EXEC [dbo].[sp2] @param
    .
    .
END

Then output is like this

sp1 output:

Male_Emp_Count       Female_Emp_Count 
--------------      ---------------- 
1                    0

Sp2 output:

empId               empJoiningDate                            
-----------         --------------
17                  2019-07-10

. . .

I want these results in single dataset row as a response to my API.

When I tried inserting into a table variable, I get an error

An INSERT EXEC statement cannot be nested

What is this error and how to handle this error? I tried google but no relevant article or info that helps me to understand this issue.

CREATE PROCEDURE [dbo].[MAster_sp]
    (@param INT) 
AS
BEGIN
    CREATE TABLE #tempMainTbl 
    (
          c1 INT NULL,
          c2 INT NULL,
          c3 INT NULL,
          c4 INT NULL,
          c5 INT NULL
    )

    INSERT INTO #tempMainTbl (c1, c2, c3)
        EXEC [dbo].[sp1] @param 

    EXEC [dbo].[sp2] @param
    .
    .

    SELECT * from #tempMainTbl 
END

Error:

Msg 8164, Level 16, State 1, Procedure dbo.[sp1], Line 33 [Batch Start Line 8]
An INSERT EXEC statement cannot be nested.

Actual result that I got now:

sp1 Output

Male_Emp_Count Female_Emp_Count 
-------------- ---------------- 
1              0

Sp2 Output

empId       empJoiningDate                            
----------- --------------
17          2019-07-10

Expected result:

Male_Emp_Count       Female_Emp_Count     empId      empJoiningDate                                
--------------       ----------------     -----      --------------
1                     0                    17         2019-07-10

All stored procedures output in single row.

Possible duplicate of [An INSERT EXEC statement cannot be nested. Dealing with error](https://stackoverflow.com/questions/44051283/an-insert-exec-statement-cannot-be-nested-dealing-with-error) — SMor, Jul 08 '19 at 14:56
Tag correctly - code is generally always specific to the database engine. Presumably you are using SQL Server. And make an effort - searching the internet for that specific error message will find many discussions. — SMor, Jul 08 '19 at 14:56
@SMor i tag correctly still not figure out the problem, i'll check your tagged question for better reference, thanks Smor for your valuable comment :) — Shubham Sharma, Jul 08 '19 at 15:02

score 0 · Accepted Answer · answered Jul 08 '19 at 15:41

So this issue has to do with having a stored procedure with a insert into, being called by another stored procedure that does the insert into.

The only solution really is to merger the procedures.

Here's what you probably have right now: MAster_sp:

INSERT INTO #tempMainTbl (c1,c2,c3)
EXEC [dbo].[sp1] @param

sp1:

Insert into #someothertemptable
exec someotherprocedure @param

You just can't do that unfortunately in SQL server. Here's why and a work around: https://www.sqlservercentral.com/forums/topic/how-to-resolve-an-insert-exec-statement-cannot-be-nested-errorproblem

Here's the a similar question in a different post: Errors: "INSERT EXEC statement cannot be nested." and "Cannot use the ROLLBACK statement within an INSERT-EXEC statement." How to solve this?

thanks, now i can better understand the problem. (y) – Shubham Sharma Jul 09 '19 at 05:44 — Shubham Sharma, Jul 09 '19 at 05:44

Executing multiple sub stored procedures in a master stored procedure

1 Answers1