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:

1. 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"

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 didnt work out as well. Table variable passed as parameter cannot be modified within the stored proc.

Pls. suggest what can de done?