INSERT EXEC statement cannot be nested

Question

INSERT EXEC statement cannot be nested

Botond

SSC Eights!

Points: 838
More actions
June 26, 2003 at 12:46 am

#80658

Is this true?
And in wich context?
Because I am nesting Insert Exec statements... just at the 2nd level of nesting I get this error.
I am doing something like this:
in "StoredProc1"
BEGIN
....
select something -- it has a structure (let's say) STRUCT1
END
in "StoredProc2"
BEGIN
....
create #tmp1(...STRUCT1...)
while (condition true)
begin
delete from #tmp1
Insert into #tmp1 exec StoredProc1 -- this line will be named as the one
-- beeing the cause of the error in SP3
end
select * from #tmp1
END
in "StoredProc3"
BEGIN
.....
create #tmp2(...STRUCT1...)
insert into #tmp2 Exec StoredProc1
create #tmp3(...STRUCT1...)
if (condition TRUE)
insert into #tmp3 Exec StoredProc1
else
insert into #tmp3 Exec StoredProc2
end
END
In "StoredProc3" when condition is true, so "StoredProc1" is executed, everything works just fine, when condition gets false, and "StoredProc2" should execute, I got this error "INSERT EXEC statement cannot be nested", error in StoredProc2 at the line which I marked in the description of "StoredProc3".
Any comments would help, any ideas about nesting these statements, or alternative solutions.
Boti

🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Melville SSCommitted Points: 1985 More actions · Answer 1

It must be true because that's what it says.

Apologies in advance because I must be being dumb, but can't you just remove 'StoredProc2' as it is only getting the output from 'StoredProc1'?

Other options you could use are:

global temporary tables (##) - although I've yet to use one yet.

derived tables

I think if you consolidate your query into one sp then your problems will go away (not all of them just your SQL problems).

Botond SSC Eights! Points: 838 More actions · Answer 2

You are wright. From the example I just gave, this is the conclusion.

But I simplified my problem in this example.

Believe me, in may case SoredProc2 is necessary, because it is calling StoredProc1 with different parameters, till I get the result I wanted from StoredProc1.

Actually in the mean time I solved the problem without "StoredProc3", and the solution I came up, better suites my needs then with "StoredProc3" (in this case), but I thought, that if I ran into this problem, let's get a solution (if it exists), so next time I will know what to do. :))

And, also, you are wright again, when you are talking about consolidating my query in one SP.

I my case the issue was not how can I solve this particular case, but how can I reuse the result of one SP in another, and the second SP's result in a third, and so on... with this thehnique or something similar.

The ideal way would be that I could reuse an SP's result in SQL statements, just like I use a View's result.

Best regards,

Boti 🙂

Thanx for your time and effort!

🙂

hurcane SSChasing Mays Points: 623 More actions · Answer 3

We've had the nested insert problem in a number of places in our stored procedures. Here's our solution. Perhaps somebody has a better one...

The first procedure does not perform an insert exec. It creates a temp table with a specific name (e.g. #PriceResults)

The second procedure looks for the existence of this table with a line like this:

If OBJECT_ID('tempdb..#PriceResults') is not null

Set @CallingProcCreatedTable = 1

Else

Set @CallingProcCreatedTable = 0

In the second procedure, if @CallingProcCreatedTable = 0, we execute our own Create Table statement.

Still in the second procedure, we execute our insert exec into #PriceResults.

Still in the second procedure, if @CallingProcCreatedTable = 0, we select the data out of the temp table and then drop it. Otherwise, we do nothing, which leaves the data in the temp table for the calling procedure to use.

This allows the second procedure to be used one of two ways. It can be used remotely, whereby it returns standard query results. For pricing information, this might be done by an order entry client (windows app or a web page). It can also be used by another stored procedure that knows to create the temp table. This might be done by a "repricing" procedure that is looping through all the lines on an order.

I hope this has been of assistance.

Botond SSC Eights! Points: 838 More actions · Answer 4

Thank you, hurcane, for your help!

It always helps to see some new solutions!

Best regards,

Botond

🙂