INSERT EXEC statement cannot be nested

Question

I have a stored proc spGetSites that returns three columns for SiteName, SiteId and UnitCount. The stored proc runs perfectly fine. I'm trying to store the results of the stored proc to a temp table @Site to use for my report with below syntax. This time I got an error message: "INSERT EXEC statement cannot be nested"

Can I not use INSERT and EXEC statement at the same time?

DECLARE @Site TABLE (SiteName VARCHAR(100), SiteId INT, UnitCount INT)

INSERT INTO @Site
EXEC spGetSites @SiteId = 0

Answer 1

If you look at the code for your spGetSites procedure, somewhere in that procedure is another INSERT...EXEC. It may be directly in that procedure, or nested in the bowels of some other procedure it calls.

Ultimately, if a stored procedure uses INSERT...EXEC then if you try to call that stored procedure in the context of another INSERT...EXEC you will get the error you are seeing.

How do you fix it?

1. You could simply take the inner INSERT...EXEC and inline the code to this single stored procedure. Though, I suspect that other procedure may be there for a reason: ie to keep your code DRY.

2. Since this is a Get procedure, hopefully there's no data manipulation happening anywhere in the call stack. You could convert the child procedure into a table-valued function. This would allow you to convert that inner INSERT...EXEC into an INSERT...SELECT and resolve this issue.

3. Use temp tables scoped to the outer procedure to pass data between procedures. This solution gets complicated, so it's not my favorite, and I generally discourage this pattern when there's a better option--but for the sake of completeness, I'll include it here. Basically, if you create your #temp table outside spGetSites you can use it inside spGetSites (without creating it inside there), and the table with data inserted will survive the procedure execution and continue to work.

I don't like option 3 because it's a coding pattern complex enough to ensure someone messes it up in the future, unless everyone is on board and familiar with the coding pattern: * spGetSites will fail unless you create the table first. All callers need to remember to create the table exactly the same first. * spGetSites can't assume the table is empty. It might have existing data from the outer call (or a prior execution from the same caller) * Troubleshooting and debugging (and even getting a query plan) for spGetSites is more complex because of the table creation confusion.

What would I do?

Without knowing how complex the code is behind spGetSites, I'd look into creating an inline TVF that replaces the inner INSERT...EXEC with INSERT...SELECT or possibly all of spGetSites could be simplified/rewritten to make it self contained without the INSERT...EXEC