CODEWITHSUNDEEP
sql-serverstored-procedures
shanlodh
shanlodh

Reputation: 1045

SQL Server: capture temp table from stored procedure that calls other stored procedures

I have a stored procedure that takes one parameter, passing it to stored procedures within it. The result set is a temptable from a view into these stored procedure outputs. The stored procedure is running fine, but I'm having trouble capturing its result set: 

use mydb
go

create procedure sp_myproc (@refdate date)
as
begin
    declare @temptable table 
                       (
                            field1 varchar(50), 
                            field2 varchar(50), 
                            field3 varchar(50), 
                            field4 varchar(30), 
                            field5 numeric(20, 0)
                       )

    exec sp_firstsp @refdate 
    exec sp_secondsp @refdate
    exec sp_thirdsp @refdate

    exec sp_fourthsp @refdate
    exec sp_fifthsp

    insert into @temptable  
        select field1, field2, field3, field4, field5 
        from view_into_results_of_abovesps 

    -- return @temptable (error: Must declare the scalar variable "@temptable".)
end
go

I'm trying to capture the result set in the following manner but getting an empty table: 

    declare @temptable table 
                       (
                            field1 varchar(50), 
                            field2 varchar(50), 
                            field3 varchar(50), 
                            field4 varchar(30), 
                            field5 numeric(20, 0)
                       )

    insert into @temptable
        execute sp_myproc '2017-01-13'

Thanks

Upvotes: 0

Views: 54

Answers (1)

Fleshy
Fleshy

Reputation: 182

use mydb
go

create procedure sp_myproc (@refdate date)
as
begin
declare @temptable table 
                   (
                        field1 varchar(50), 
                        field2 varchar(50), 
                        field3 varchar(50), 
                        field4 varchar(30), 
                        field5 numeric(20, 0)
                   )

exec sp_firstsp @refdate 
exec sp_secondsp @refdate
exec sp_thirdsp @refdate

exec sp_fourthsp @refdate
exec sp_fifthsp

insert into @temptable  
    select field1, field2, field3, field4, field5 
    from view_into_results_of_abovesps 

--USE THIS
select * from @temptable

end

Upvotes: 2

Related Questions