An INSERT EXEC statement cannot be nested.

最新推荐文章于 2022-05-18 21:57:15 发布

weixin_34175509

最新推荐文章于 2022-05-18 21:57:15 发布

阅读量997

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/Wison-Ho/p/3993169.html

版权

今天开发同事有咨询问题，报错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

转载于:https://www.cnblogs.com/Wison-Ho/p/3993169.html