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.
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.
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.
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.
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:
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With