Home/ Questions/Error: an insert exec statement cannot be nested – quick tips
Next
Answered
Stella O'Ryan
  • 33
Asked: 2022-05-18T16:22:36+00:00 In:

Error: an insert exec statement cannot be nested – quick tips

  • 33

. Advertisement .

..3..

. Advertisement .

..4..

The error

INSERT EXEC statement cannot be nested
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

occurred when I execute the Sp1, it will display the error (I have 3 stored procedures Sp1, Sp2 and Sp3. The first (Sp1) will run the second (Sp2) which will save the returned data to @tempTB1 while the second will execute the third (Sp3) as well as save the data in @tempTB2.). I’ve tried to fix it for some days but have not resolved it yet. Can someone suggest a relevant solution to fix the an insert exec statement cannot be nested issue? Much appreciate your support.

insert exec
Share

2 Answers

  1. Best Answer
    Expert

    The cause:

    It looks like you are trying to ‘bubble up’ data from a series of procedures which are stored, but there is a restriction on SQL Server that you can only have one active INSERT-EXEC at a given time. Therefore, this error happens.

    Solution:

    To find the way solve this error, you can read this article: How To Share Data Between Stored Procedures. It is a detailed article about patterns that can be used to solve this problem.

    Another solution I recommend you is converting Sp3 into a Table value function.

    • 9

  2. This is the only way to do it in SQL Server without having to create a huge convoluted function or execute sql string calls, which would be terrible.

    1. Create a temp table
    2. openrowset your stored procedure data into it

    EXAMPLE: 

    INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')

    Note – You must use’setfmtonlyoff’ and you can’t add dynamic sql inside the openrowset calls, either for the string that contains your stored procedure parameters, or for the table’s name. You must use temp tables instead of table variables. It is more efficient in most cases.

    • 9

Leave an answer

You must login to add an answer.

Need An Account,