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

Question

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.

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/206482/discussion-on-question-by-neelabh-pass-a-table-variable-using-table-type-into-a). — Samuel Liew, Jan 23 '20 at 00:52

Nguyễn Văn Phong · Accepted Answer · 2020-01-22T09:39:28.657

2

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

edited Jan 22 '20 at 09:39

answered Jan 22 '20 at 09:29

Nguyễn Văn Phong

13,506
17
39
56

what does 'e' here refer to? – Neelabh Jan 22 '20 at 09:33
it's a table alias according to `dbo.employee` – Nguyễn Văn Phong Jan 22 '20 at 09:33

TT. · Answer 2 · 2020-01-22T09:50:23.300

As far as I can tell, the update statement should read

CREATE PROCEDURE updateEmpDetails
    @UpdateType UpdateTableType READONLY
AS
BEGIN    
    UPDATE 
        e
    SET 
        Designation = u.Designation,
        Salary = u.Salary
    FROM
        dbo.employee AS e
        INNER JOIN @UpdateType AS u ON
            u.Id=e.[Employee ID];
END

So join the employee table with the table variable with this way of writing the update statement.

The e in the update statement is the table alias assigned to dbo.employee in the FROM clause. I could also have written UPDATE dbo.employee [...], that would work as well. Note that the FROM clause can have multiple references to dbo.employee (for some more complex statements) in which case you should definitely use the alias to denote which should be used as the anchor for updates. I just always use the alias, makes SQL statements more uniform.

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

2 Answers2