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?
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.
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.
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...SELECT or possibly all of
spGetSites could be simplified/rewritten to make it self contained without the