nested insert exec work around
- by stackoverflowuser
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 created 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 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:
within usp_Decision created a temp table and tried to do "insert into #tt exec usp_SP1". Didn't work out. error "insert exec cannot be nested"
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 didnt work out as well. Table variable passed as parameter cannot be modified within the stored proc.
Pls. suggest what can de done?
