Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Errors: "INSERT EXEC statement cannot be nested." and "Cannot use the ROLLBACK statement within an INSERT-EXEC statement." How to solve this?

I have three stored procedures Sp1, Sp2 and Sp3.

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 @tempTB2.

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.

like image 635
HAJJAJ Avatar asked Sep 25 '10 19:09

HAJJAJ


People also ask

How resolve an insert exec statement Cannot be nested in SQL Server?

There are three ways to work around this error. The first option is to integrate the two stored procedures together into a single stored procedure. This option is possible if there are no other stored procedures, scripts or applications that are using either of the stored procedures to be merged.

How do you query the results of a stored procedure?

The only way to work with the results of a stored procedure in T-SQL is to use the INSERT INTO ... EXEC syntax. That gives you the option of inserting into a temp table or a table variable and from there selecting the data you need.


3 Answers

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.

like image 182
eddiegroves Avatar answered Oct 18 '22 23:10

eddiegroves


This is the only "simple" way to do this in SQL Server without some giant convoluted created function or executed sql string call, both of which are terrible solutions:

  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 'set fmtonly off', AND you CANNOT add dynamic sql to this either inside the openrowset call, either for the string containing your stored procedure parameters or for the table name. Thats why you have to use a temp table rather than table variables, which would have been better, as it out performs temp table in most cases.

like image 30
Mitch Stokely Avatar answered Oct 18 '22 22:10

Mitch Stokely


My work around for this problem has always been to use the principle that single hash temp tables are in scope to any called procs. So, I have an option switch in the proc parameters (default set to off). If this is switched on, the called proc will insert the results into the temp table created in the calling proc. I think in the past I have taken it a step further and put some code in the called proc to check if the single hash table exists in scope, if it does then insert the code, otherwise return the result set. Seems to work well - best way of passing large data sets between procs.

like image 16
Matt Luckham Avatar answered Oct 18 '22 21:10

Matt Luckham