The exec nested insert works

Question

I have 2 stored procedures usp_SP1 and usp_SP2. Both of them use insertion in #tt exec sp_somesp. I wanted to create a third stored procedure that decides which stored proc to call. Sort of:

create proc usp_Decision
(
   @value int
)
as
begin
   if (@value = 1)
     exec usp_SP1  -- this proc already has insert into #tt exec usp_somestoredproc
   else
        exec usp_SP2  -- this proc too has insert into #tt exec usp_somestoredproc
end

Later I realized that I needed a specific structure for the return value from usp_Decision so that I could populate the SSRS dataset field. So here is what I tried:

• A temporary table was created inside usp_Decision and attempted to "insert in #tt exec usp_SP1". This did not work. "insert exec cannot be nested" error

• usp_Decision proc procs "select * from". . , , .

, , .

Answer 1

usp_SP1 usp_SP2?

, usp_Decision , :

create table #results (....)

. , . , , . , .

if object_id('tempdb..#results') is not null begin
  insert #results (....)
  select .....
end
else begin
  select ....
end

, # results proc, , .

, usp_Decision.

Answer 2

( ).

proc temp * . temp proc.

esimple

create proc usp_mytest1
as
select top 1 id into #test1
from MYDATABASE..MYTABLE (nolock)

select * from #test1
go
--drop table #test
create proc usp_mytest2
as
select top 10 MYTABLE_id into #test2
from MYDATABASE..MYTABLE (nolock)

select * from #test2
go

create proc usp_mytest3 (@myvalue int)
as
create table #test3 (MYTABLE_id int)
if @myvalue = 1
Begin
insert #test3
exec ap2work..usp_mytest1
end
else
begin
insert #test3
exec ap2work..usp_mytest2
end

select * from #test3

go

exec ap2work..usp_mytest3 1

exec ap2work..usp_mytest3 0

Answer 3

, ( , )? HLGEM, , .

Answer 4

. wortkaround ( OPENROWSET, loopback-, INSERT EXEC)