title: An INSERT EXEC statement cannot be nested. Dealing with error
body: <p>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 (<code>using       EXEC sp_MSforeachdb @sql)</code>. And I can't use it, because in OPENROWSET you have to specify from which db you are running procedure</p>
  
  <p>Suppose I have these two SPs:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>when I run <code>exec test1 @ProcName = 'Test2'</code>, it returns error:</p>
  
  <blockquote>
    <p>An INSERT EXEC statement cannot be nested.</p>
  </blockquote>
  
  <p>The problem here is that I can't stop using <code>insert exec</code> in <code>test2</code>. And test1 should have @ProcName as a variable</p>
  
  <p>What is the least painless way to resolve this issue?</p>