SQL Server – Insert Stored Procedure Results into Table

sql serverstored-procedurestabletemporary-tables

I have a stored procedure that I want to insert into a table, the stored procedure however has parameters I have to pass, how can I do this?

DECLARE @RC int
DECLARE @startweek varchar(20)
DECLARE @endweek varchar(20)
DECLARE @payroll varchar(30)
DECLARE @job varchar(25)
DECLARE @job_to varchar(25)
DECLARE @manager varchar(30)
DECLARE @office varchar(100)
DECLARE @pu varchar(6)
DECLARE @pu_to varchar(6)
DECLARE @task varchar(25)
DECLARE @task_to varchar(25)
DECLARE @Prj_pu varchar(6)
DECLARE @Prj_pu_to varchar(6)

-- TODO: Set parameter values here.
SET @endweek = dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate());
SET @startweek = DATEADD(WEEK, -25, @endweek)

EXECUTE @RC = [dbo].[TIME_lynx_extract] 
   @startweek
  ,@endweek 
  ,@payroll
  ,@job
  ,@job_to
  ,@manager
  ,@office
  ,@pu
  ,@pu_to
  ,@task
  ,@task_to
  ,@Prj_pu
  ,@Prj_pu_to
GO

I want to insert the result of this (the calling of the stored procedure) into a table, I have created the table with the columns, but if there were a way with temp table even better.

Best Answer

Here is an example of inserting the results of a store procedure call into a table variable (of course,you can use a real table). The stored procedure takes 2 parameters and returns their values as a result set.

--demo setup
drop procedure if exists [dbo].[sp1]
go
CREATE PROCEDURE [dbo].[sp1] @P1 INT
    ,@P2 INT
AS
BEGIN
    SELECT @p1 AS p1
        ,@p2 AS p2
END
go

--create table variable to hold results of SP call
Declare @T table (P1 int, P2 int)

--insert into the table variable the results of the SP call
Declare @P1 int = 1, @P2 int = 2
insert into @T
exec [dbo].[sp1] @P1, @P2

--select from the table variable to verify
select * from @T

P1  P2
1   2
Related Question