October 31, 2018 at 3:24 pm
Hello,
I have a rather complex stored procedure that is used for one of our reports. We'll call it "First Sproc".
Eventually, our users wanted a new report, which would use the results from First Sproc as a starting point and filter what is unneeded while grouping the data differently and including new columns and calculations. We created a temp table and used INSERT #TempTable EXEC FirstSproc as part of this stored procedure. This worked fine. We'll call it "Second Sproc".
Now, a third report is being requested, one which is based on the results from Second Sproc, with a few filtering changes and new columns. When I try to use INSERT #TempTable EXEC SecondSproc, I encounter an error:
"An INSERT EXEC statement cannot be nested."
If I understand correctly, SQL Server can't use the INSERT EXEC pattern through multiple layers of stored procedures at a time. It can happen only once.
How do others avoid this situation, assuming that there is no way to consolidate the three different reports into a single stored procedure? How do I create this third stored procedure such that it depends on the second (which itself depends on the first)? I know I can work around this by creating a new sproc that includes all of the combined logic from First Sproc and Second Sproc, but I don't want to create the maintenance nightmare of having the same code in multiple places.
I appreciate any thoughts on this! I realize I didn't post any code, but I'm just looking for general thoughts and discussion at this point.
October 31, 2018 at 5:36 pm
Try converting your procedure(s) to an inline table valued function(s).
October 31, 2018 at 5:50 pm
create another master SP
parameter sp_level int
this requires that SP2 and SP3 do not call the previous sp, but rather depend on it having been executed previously and results stored on the desired temp table.
calling this master sp with the correct level would also allow calling code to execute a single proc just passing a different sp level to execute the desired procs
base logic would beif sp_level > 0
begin
declare temp_sp1
insert into temp_sp1
exec firstsp
end
if sp_level > 1
begin
declare table temp_sp2
insert into temp_sp2
exec secondsp
end
if sp_level > 2
begin
declare table temp_sp3
insert into temp_sp3
exec thirdsp
end
if sp_level = 1
begin
select *
from temp_sp1
end
if sp_level = 2
begin
select *
from temp_sp2
end
if sp_level = 3
begin
select *
from temp_sp3
end
November 1, 2018 at 7:30 am
Thank you both for the suggestions! I will give these a try.
November 1, 2018 at 1:36 pm
TVFs are definitely the preferable option, INSERT-EXEC is something of an anti-pattern and has a bunch of drawbacks aside from the nesting one you're experiencing here. You can't always avoid it, but it's definitely better to if there is an alternate solution.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply