An INSERT EXEC statement cannot be nested.

今天开发同事有咨询问题，报错An INSERT EXEC statement cannot be nested.

经过在网上查询，最终找到解决方案，具体见:http://www.sommarskog.se/share_data.html#INSERTEXEC

Demo如下：

use demo 
go 
alter proc usp_a @a int 
as 
create table #a(id int,editdt datetime) 
insert into #a 
        select @a,getdate() 
select * from #a   

alter proc usp_b 
as 
create table #c (id int,editdt datetime) 
insert into #c exec usp_a '2' 
select * from #c 


create table #d (id int,editdt datetime) 
insert into #d exec usp_b 
select * from #d

　　

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

 

可以采用如下的方式解决：

use demo
go
alter proc usp_c @a int
as
insert #c select @a,getdate()

alter proc usp_d
as
create table #c(id int,editdt datetime)
exec usp_c '2'
select * from #c

 

create table #e (id int,editdt datetime)
insert into #e exec usp_d
select * from #e