INSERT EXEC 语句不能嵌套.处理错误 [英] An INSERT EXEC statement cannot be nested. Dealing with error

问题描述

不重复.这里的问题是它有不同的结构.我对我能改变的和不能改变的有一些限制.这里我有确切的例子，其他票没有.也不能应用 OPENROWSET，因为 test1 循环遍历所有数据库(使用 EXEC sp_MSforeachdb @sql).而且我不能使用它，因为在 OPENROWSET 中你必须指定你正在运行程序的数据库

Not duplicate. The problem here is it has different structure. and I have some restrictions of what I can change and what I can't. Here I have exact example, which other tickets don't. OPENROWSET cannot be applied either, because test1 loops through all dbs (using EXEC sp_MSforeachdb @sql). And I can't use it, because in OPENROWSET you have to specify from which db you are running procedure

假设我有这两个 SP:

Suppose I have these two SPs:

create procedure test1
@ProcName  varchar(155)
as
begin

if OBJECT_ID('tempdb..#testt') is not null drop table #testt
create table #testt1(a int, b int)

insert #testt1
exec @ProcName

select * from #testt1
end

create procedure test2  
as
begin

declare @sql varchar(155)

if OBJECT_ID('tempdb..#testt2') is not null drop table #testt2
create table #testt2(a int, b int)

select @sql = 'select 1 as a,2 as b'

insert #testt2
exec (@sql)

select * from #testt2

end

当我运行 exec test1 @ProcName = 'Test2' 时，它返回错误:

when I run exec test1 @ProcName = 'Test2', it returns error:

不能嵌套 INSERT EXEC 语句.

An INSERT EXEC statement cannot be nested.

这里的问题是我无法停止在 test2 中使用 insert exec.而 test1 应该有 @ProcName 作为变量

The problem here is that I can't stop using insert exec in test2. And test1 should have @ProcName as a variable

解决此问题的最轻松的方法是什么?

What is the least painless way to resolve this issue?

推荐答案

Putting insert 语句到 test2 程序中解决了问题

Putting insert statement inside test2 procedure solved problem

这篇关于INSERT EXEC 语句不能嵌套.处理错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！