CODEWITHSUNDEEP
sql-serverstored-procedures
snowneji
snowneji

Reputation: 1136

SQL Server stored procedure as input error

I have the following stored procedure and create a customized type and then feed in a table as a parameter to my stored procedure to update my target table:

-- Create Type
CREATE TYPE dbo.e2m_dt AS TABLE
(
    [ID] BIGINT,
    [text] VARCHAR(max)
)

-- Create Procedure:
CREATE PROCEDURE MERGE_REQUEST_RESULT  
    @temp_table dbo.e2m_dt READONLY
AS
    UPDATE Table1
    SET ID = @temp_table.ID,  
        text = @temp_table.text
    FROM Table1
    INNER JOIN @temp_table ON Table1.ID = @temp_table.ID
GO

But I'm getting the following errors:

Msg 137, Level 16, State 1, Procedure MERGE_REQUEST_RESULT, Line 9
Must declare the scalar variable "@temp_table".

Msg 137, Level 16, State 1, Procedure MERGE_REQUEST_RESULT, Line 10
Must declare the scalar variable "@temp_table".

Msg 137, Level 16, State 1, Procedure MERGE_REQUEST_RESULT, Line 16
Must declare the scalar variable "@temp_table".

I'm fairly new to SQL, any help will be appreciated.

Upvotes: 0

Views: 103

Answers (1)

Thom A
Thom A

Reputation: 95571

ID = @temp_table.ID would refer to a scalar variable @temp_Table not the table type variable @temp_table. Alias your objects and use those:

CREATE PROCEDURE MERGE_REQUEST_RESULT  @temp_table dbo.e2m_dt READONLY
AS
        UPDATE T1
        SET ID = tt.ID,  
            [text] = tt.[text]
        FROM Table1 T1
        INNER JOIN @temp_table tt ON T1.ID = tt.ID;

GO

Side note, I recommend against calling your varchar(MAX) column text. text is a deprecated data type. Stick to non keywords for object names.

Upvotes: 2

Related Questions