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
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
when I run exec test1 @ProcName = 'Test2', it returns error:
An INSERT EXEC statement cannot be nested.
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?