Posted By: Anonymous
I have three stored procedures
The first one (
Sp1) will execute the second one (
Sp2) and save returned data into
@tempTB1 and the second one will execute the third one (
Sp3) and save data into
If I execute the
Sp2 it will work and it will return me all my data from the
Sp3, but the problem is in the
Sp1, when I execute it it will display this error:
INSERT EXEC statement cannot be nested
I tried to change the place of
execute Sp2 and it display me another error:
Cannot use the ROLLBACK statement
within an INSERT-EXEC statement.
This is a common issue when attempting to ‘bubble’ up data from a chain of stored procedures. A restriction in SQL Server is you can only have one INSERT-EXEC active at a time. I recommend looking at How to Share Data Between Stored Procedures which is a very thorough article on patterns to work around this type of problem.
For example a work around could be to turn Sp3 into a Table-valued function.