CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
DBA108642
DBA108642

Reputation: 2112

SQL Server stored procedure input multiple variables into a temp table

I need to create a temporary table and populate it with temporary values. The variables have values assigned from a python script. My code is as follows:

ALTER PROCEDURE [dbo].[AddScrapedInfoBULK] 
    (
    -- Parameters for the SP, (each field in the all tables)
    -- ProjectInfo Fields
    @ProjectInfoID AS INT,
    @OrderNumber AS NVARCHAR(255),
    @PeriodofPerformance AS NVARCHAR(255),
    @POPEndDate AS DATETIME,
    @PopStartDate AS DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @temproj TABLE (ProjectInfoID INT,
                            OrderNumber NVARCHAR(255),
                            PeriodofPerformance NVARCHAR(255),
                            POPEndDate DATETIME,
                            PopStartDate DATETIME)

    INSERT INTO @temproj 
        SELECT (@ProjectInfoID,
                @OrderNumber,
                @PeriodofPerformance,
                @POPEndDate,
                @PopStartDate)
END

but this does not work. How can I populate a temporary table with variables?

Upvotes: 0

Views: 305

Answers (2)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

Remove the parentheses around the select.

DECLARE    @ProjectInfoID AS INT,
    @OrderNumber AS NVARCHAR(255),
    @PeriodofPerformance AS NVARCHAR(255),
    @POPEndDate AS DATETIME,
    @PopStartDate AS DATETIME

    SET NOCOUNT ON;

    DECLARE @temproj TABLE (ProjectInfoID INT,
                            OrderNumber NVARCHAR(255),
                            PeriodofPerformance NVARCHAR(255),
                            POPEndDate DATETIME,
                            PopStartDate DATETIME)

    INSERT INTO @temproj 
        SELECT @ProjectInfoID,
                @OrderNumber,
                @PeriodofPerformance,
                @POPEndDate,
                @PopStartDate

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can just use insert into .... values to make it.

INSERT INTO @temproj 
            (projectinfoid, 
             ordernumber, 
             periodofperformance, 
             popenddate, 
             popstartdate) 
VALUES      (@ProjectInfoID, 
             @OrderNumber, 
             @PeriodofPerformance, 
             @POPEndDate, 
             @PopStartDate)

Upvotes: 3

Related Questions