0

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.

  • Based on your code, I updated your question to tag SQL Server. – Scott Hodgin Aug 16 '19 at 12:50
  • I don't see what the problem is, could you please explain? What prevents you from using the INSERT ... EXECUTE syntax, which would seem to me (and probably to many others) the obvious solution? What does a temporary table have to do with this? You can (drop & re)create a temporary table too. Please elaborate. – Andriy M Aug 16 '19 at 13:24
  • it was giving me error, I believe due to bad formatting from my side, but the script model that @ScottHodgin provided worked great, his response was the best for it :) – Baldie47 Aug 16 '19 at 21:48
3

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
| improve this answer | |
  • I understand this, but this works if the stored procedure doesn't have parameters in my case it forces me to declare scalar variable and send the value of the parameter when I try to do the exec – Baldie47 Aug 16 '19 at 13:00
  • @gasguirre - I updated my example to show how you can pass in parameters to the stored procedure and insert the results from the stored procedure into a table variable (which could also be a temporary table or a real table. Please update your question to provide some more information as to why this solution does not work for you. – Scott Hodgin Aug 16 '19 at 13:34
  • Now it is working :) thank you! I was ale to make it work now with a tale I created, don't know how to do it with a temp table so I don't have to have an object on the DB, but for now with this it works, thank you very much! – Baldie47 Aug 16 '19 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.