October 24, 2017 at 1:14 pm
Hello all. fairly new to SQL. but not that new.
I have 3 stored procedures.
The first one has a statement like "Insert into #TblZBKSP Exec m2maux01..sp_calcbookandshipreportreva @DateRangeFrom"
The second also has an insert statement from the 3rd procedure.
I am getting "An INSERT EXEC statement cannot be nested."
I fully understand this error but I can't figure away around it.
I can't change the second procedure to remove the inserts because it is used elsewhere.
I also tried Openrowset and Openquery to no avail.
Any help would be appreciated.
Bill
October 24, 2017 at 1:36 pm
There's only one way around that problem. You can NOT nest any INSERT / EXEC combinations, so stop doing it. Nested stored procedures, while handy for separating logic, are not such a good idea in the long run and particularly if you're going to insist on inserting things in the middle of an insert and using a stored procedure to do so. You have no alternative that I'm aware of but to eliminate the nested sproc and move that code up into the calling stored procedure.
Given that you've designed this to operate that way, you may want to re-think how you go about the process and do some serious re-design, so that you stop doing things like that. If you've got code you can post, someone here may be able to help you with the redesign.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 24, 2017 at 2:31 pm
In the past, I have faced a similar issue, and not been able to change the underlying procedure.
So I added an optional xml output parameter.
The pattern that causes the issue.CREATE PROCEDURE proc1
AS
BEGIN
create #T2 ...
-- This causes the error
insert into #T2(...)
exec proc2
END
GO
CREATE PROCEDURE proc2
AS
BEGIN
create #T ...
insert into #T (...)
exec someOtherProc
END
GO
The pattern used to work around it.CREATE PROCEDURE proc1
AS
BEGIN
declare @outData xml;
create #T2 ...
exec proc2 @outData = @outData output;
insert into #T2(...)
select ...
from @outData.nodes .....
END
GO
CREATE PROCEDURE proc2
@outData xml = null output
AS
BEGIN
create #T ...
insert into #T (...)
exec someOtherProc
select @outData = (select * from #T for xml ...);
END
GO
October 24, 2017 at 2:38 pm
DesNorton - Tuesday, October 24, 2017 2:31 PMIn the past, I have faced a similar issue, and not been able to change the underlying procedure.
So I added an optional xml output parameter.The pattern that causes the issue.
CREATE PROCEDURE proc1
AS
BEGIN
create #T2 ...-- This causes the error
insert into #T2(...)
exec proc2
END
GO
CREATE PROCEDURE proc2
AS
BEGIN
create #T ...insert into #T (...)
exec someOtherProc
END
GOThe pattern used to work around it.
CREATE PROCEDURE proc1
AS
BEGIN
declare @outData xml;create #T2 ...
exec proc2 @outData = @outData output;
insert into #T2(...)
select ...
from @outData.nodes .....
END
GO
CREATE PROCEDURE proc2
@outData xml = null output
AS
BEGIN
create #T ...insert into #T (...)
exec someOtherProcselect @outData = (select * from #T for xml ...);
END
GO
I like the idea, but understand that xml processing can a performance killer with any sizable number of rows or quantity of data. Your mileage may vary...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 24, 2017 at 3:55 pm
Really need to see the code. Without it, we are shooting in the dark.
October 25, 2017 at 5:38 am
sgmunson - Tuesday, October 24, 2017 1:36 PMThere's only one way around that problem. You can NOT nest any INSERT / EXEC combinations, so stop doing it. Nested stored procedures, while handy for separating logic, are not such a good idea in the long run and particularly if you're going to insist on inserting things in the middle of an insert and using a stored procedure to do so. You have no alternative that I'm aware of but to eliminate the nested sproc and move that code up into the calling stored procedure.Given that you've designed this to operate that way, you may want to re-think how you go about the process and do some serious re-design, so that you stop doing things like that. If you've got code you can post, someone here may be able to help you with the redesign.
Steve, I did not design this. I inherited it. It used to be Foxpro code that called the SPROC and got the data into a Foxpro cursor. Now our ERP software is moving to 100% SQL and I need to redo the report in SQL. As you state, I think my only choice is to redesign (Create a different SPROC along side the exising one till we are 100% upgraded.) But I am interested in a post someone else posted. I will have to investigate how that is done.
October 25, 2017 at 5:44 am
Lynn Pettis - Tuesday, October 24, 2017 3:55 PMReally need to see the code. Without it, we are shooting in the dark.
Lyn, its about 2500 lines of code at least all put together. The example DesNorton provided is a much scaled down version but it is on point. It looks like I might do this.
Amend the second SPROC to create a table and fill the table then
Execute the SPROC in the top level SPROC
Then use a select statement to retrieve the results of the SPROC.
This is the only way I can do it quickly enough without total redesign.
Then after getting the results from the SPROC, delete the table.
October 25, 2017 at 6:51 am
bswhipp - Wednesday, October 25, 2017 5:38 AMsgmunson - Tuesday, October 24, 2017 1:36 PMThere's only one way around that problem. You can NOT nest any INSERT / EXEC combinations, so stop doing it. Nested stored procedures, while handy for separating logic, are not such a good idea in the long run and particularly if you're going to insist on inserting things in the middle of an insert and using a stored procedure to do so. You have no alternative that I'm aware of but to eliminate the nested sproc and move that code up into the calling stored procedure.Given that you've designed this to operate that way, you may want to re-think how you go about the process and do some serious re-design, so that you stop doing things like that. If you've got code you can post, someone here may be able to help you with the redesign.
Steve, I did not design this. I inherited it. It used to be Foxpro code that called the SPROC and got the data into a Foxpro cursor. Now our ERP software is moving to 100% SQL and I need to redo the report in SQL. As you state, I think my only choice is to redesign (Create a different SPROC along side the exising one till we are 100% upgraded.) But I am interested in a post someone else posted. I will have to investigate how that is done.
Having also read your post to Lynn, I have to agree that the "expedient" thing is probably to follow the pattern Des laid out for you, and then revisit this nightmare later and then "clean up the mess", so to speak...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 25, 2017 at 6:58 am
sgmunson - Wednesday, October 25, 2017 6:51 AMbswhipp - Wednesday, October 25, 2017 5:38 AMsgmunson - Tuesday, October 24, 2017 1:36 PMThere's only one way around that problem. You can NOT nest any INSERT / EXEC combinations, so stop doing it. Nested stored procedures, while handy for separating logic, are not such a good idea in the long run and particularly if you're going to insist on inserting things in the middle of an insert and using a stored procedure to do so. You have no alternative that I'm aware of but to eliminate the nested sproc and move that code up into the calling stored procedure.Given that you've designed this to operate that way, you may want to re-think how you go about the process and do some serious re-design, so that you stop doing things like that. If you've got code you can post, someone here may be able to help you with the redesign.
Steve, I did not design this. I inherited it. It used to be Foxpro code that called the SPROC and got the data into a Foxpro cursor. Now our ERP software is moving to 100% SQL and I need to redo the report in SQL. As you state, I think my only choice is to redesign (Create a different SPROC along side the exising one till we are 100% upgraded.) But I am interested in a post someone else posted. I will have to investigate how that is done.
Having also read your post to Lynn, I have to agree that the "expedient" thing is probably to follow the pattern Des laid out for you, and then revisit this nightmare later and then "clean up the mess", so to speak...
To all, Thank you for your knowledge and time. I have it working (Temporarily until I can revisit it - probably never).
November 3, 2017 at 10:36 am
and your successor in 5 years time will be
"What was this guy smoking :)"
So make sure you put in comments so that it is clear it's not your fault!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply