Pass a table variable using table type into a stored procedure?

I have a table employee with around 1000 entries, I have to create a stored procedure which accepts a parameter. The parameter will most likely be of the type which I created ‘AS Table’. Upon passing the table variable into the stored procedure, it should update certain values in specific columns and the changes should reflect in the original Employee table.

I tried the following steps:

USE testDB
GO

CREATE TYPE dbo.UpdateTableType AS TABLE(Id BIGINT NULL, Designation varchar(50) NULL, Salary varchar(50) NULL)

DECLARE @employeeTable TABLE(Id BIGINT, Designation varchar(50), Salary varchar(50))

INSERT INTO @employeeTable
    VALUES 
        (2, 'Sales', '$8002'),
        (7, 'Sales DPO', '$8007'),
        (9, 'Sales Mgr', '$8009')

Select * from @employeeTable

The above is for the table type and the table variable

Now, for the stored procedure I tried,

CREATE PROCEDURE updateEmpDetails
    @UpdateType UpdateTableType READONLY
AS
BEGIN
    UPDATE dbo.employee
        SET 
            dbo.employee.Designation = @UpdateType.Designation,
            dbo.employee.Salary = @UpdateType.Salary
        WHERE
            @UpdateType.Id = dbo.employee.[Employee ID]
END

Msg 137, Level 16, State 1, Procedure updateEmpDetails, Line 7 [Batch Start Line 2] Must declare the scalar variable “@UpdateType”

Please help me as to where am I going wrong.

I need to update the Employee Table using a stored procedure that accepts table variable.

If possible please provide me with the correct code for the same.

Answer

You are passing a table type, So you need to join with it as a table instead.

UPDATE e
SET e.Designation = u.Designation,
    e.Salary = u.Salary

FROM dbo.employee e
INNER JOIN @UpdateType u ON e.[Employee ID] = u.Id

FullCode:

CREATE PROCEDURE updateEmpDetails
    @UpdateType UpdateTableType READONLY
AS
BEGIN
    UPDATE e
    SET e.Designation = u.Designation,
        e.Salary = u.Salary

    FROM dbo.employee e
    INNER JOIN @UpdateType u ON e.[Employee ID] = u.Id
END

Advertisement

Answer