nested insert exec work around

Question

I have 2 stored procedures usp_SP1 and usp_SP2. Both of them make use of insert into #tt exec sp_somesp. I wanted to create a 3rd stored procedure which will decide which stored proc to call. Something like:

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 I needed some structure defined for the return value from usp_Decision so that I can populate the SSRS dataset field. So here is what I tried:

1. Within usp_Decision created a temp table and tried to do "insert into #tt exec usp_SP1". This didn't work out. error "insert exec cannot be nested"

2. Within usp_Decision tried passing table variable to each of the stored proc and update the table within the stored procs and do "select * from ". That didn't work out as well. Table variable passed as parameter cannot be modified within the stored proc.

Please suggest what can be done.

Answer 1

Not a fan of global temp tables in any event (other processes can read these table and may interfere with the data in them).

Why not have each proc use a local temp table and select * from that table as the last step. Then you can insert into a local temp table in the calling proc.

esimple example

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 2

Have you had a look at table-valued user-defined functions (either inline or multi-statement)? Similar to HLGEM's suggestion, this will return a set which you may not have to insert any where.

Answer 3

y cant we modify in the usp_SP1 sp ,usp_SP2 itself to display the required fields for SSRS datasets

Answer 4

See this blog article for one wortkaround (uses OPENROWSET to essentially create a loopback connection on which one of the INSERT EXEC calls happens)

Answer 5

Can you modify usp_SP1 and usp_SP2?

If so, in usp_Decision, create a local temporary table with the proper schema to insert the results:

create table #results (....)

Then, in the called procedure, test for the existence of this temporary table. If it exists, insert into the temporary table. If not, return the result set as usual. This helps preserve existing behavior, if the nested procedures are called from elsewhere.

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

When control returns to the calling procedure, #results will have been populated by the nested proc, whichever one was called.

If the result sets don't share the same schema, you may need to create two temporary tables in usp_Decision.