CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduresresultset
peter
peter

Reputation: 2516

Putting Result sets into a temp table

I have a procedure called Insert and the code looks like:

Create procedure Gen_insert
As
BEGIN


create table #temp
 ( insert_stmt varchar(max) ) 
 insert into #temp  
EXEC Generate_Insert @Table = 'Admin'

 insert into #temp  
EXEC Generate_Insert @Table = 'Impas'

 insert into #temp 
 EXEC Generate_Insert @Table = 'Asui'

 insert into #temp 
 EXEC Generate_Insert @Table = 'Alstd'  

select * from #temp 

End

When I execute it I am getting following error:

Msg 8164, Level 16, State 1, Procedure Gen_Insert, Line 73
An INSERT EXEC statement cannot be nested.

Can anyone help me.

Upvotes: 0

Views: 1835

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294227

An INSERT EXEC statement cannot be nested. The error message is quite clear. You are nesting INSERT ... EXEC. statements. Either the procedures you call (Generate_Insert) use again INSERT ... EXEC or the caller of insert procedure uses it in an INSERT ... EXEC. Only you can find which is the case. As a rule of thumb, INSERT ... EXEC should be avoided, because of this and other problems.

Upvotes: 4

Related Questions