INSERT EXEC PROC不能嵌套的解决方法

INSERT EXEC PROC不能嵌套的解决方法

 

存储过程C返回一个结果集，存储过程B用INSERT/EXEC PROC将C返回的结果集存入一个临时表中并返回一个结果集，
如果存储过程A用INSERT/EXEC PROC再将B的结果集存入一个临时表，这时会报错：INSERT EXEC 语句不能嵌套。

解决方法就是：

将过程B的INSERT/EXEC PROC语句替换为
insert into #T
SELECT *
FROM OPENROWSET('MSDASQL',
       'DRIVER={SQL Server};SERVER=192.168.0.1;UID=sa;PWD=111111;Initial Catalog=Pubs',
       'EXEC pubs.dbo.pc') AS A

即可，不过好像在过程A中要先执行一下过程B才行，即在A过程的语句INSERT/EXEC PROC的前面加上exec B。
只要过程B先执行一次，以后可以不用再执行A也能够正常执行了，这样可将过程B设置为启动时执行即可（用系统过程sp_procoption设置）

create proc pC
as
select top 10 * from authors

drop proc pB
create proc pB
as
select  * into #T from authors where 1=0
--insert into #T exec pc
insert into #T
SELECT *
FROM OPENROWSET('MSDASQL',
       'DRIVER={SQL Server};SERVER=192.168.0.1;UID=sa;PWD=111111;Initial Catalog=Pubs',
       'EXEC pubs.dbo.pc') AS A
select * from #T

create proc pA
as
select  * into #T from authors where 1=0

exec pb
insert into #T exec pB
select * from #T

 

 

exec pB
exec pA

 

或者用下面的方法解决：

 

先建一个连接到自身数据库的连接服务器，如下：
EXEC sp_addlinkedserver
 @server  = 'self',
 @srvproduct  = '',
 @provider  = 'MSDASQL',
 @datasrc  = NULL,
 @location  = NULL,
 @provstr  = 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;uid=sa;pwd=111111;'

然后再开启该连接服务器的RPC功能，右击该连接服务器，单击属性，单击服务器选项，然后选中RPC和RPC输出项即可。

 

在过程A中的语句insert into #T exec pB更新为insert into #T exec self.pubs.dbo.pB即可。全部代码如下：

--创建过程C

create proc pC
as
select top 10 * from authors

--创建过程B

create proc pB
as
select  * into #T from authors where 1=0
insert into #T exec pc
select * from #T

 

--创建一个自连接的服务器
EXEC sp_addlinkedserver
 @server  = 'self',
 @srvproduct  = '',
 @provider  = 'MSDASQL',
 @datasrc  = NULL,
 @location  = NULL,
 @provstr  = 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;uid=sa;pwd=111111;'

--创建过程A

create proc pA
as
select  * into #T from authors where 1=0
insert into #T exec self.pubs.dbo.pB
select * from #T
--执行过程A，测试通过

exec pA