CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Neelabh
Neelabh

Reputation: 55

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.

Upvotes: 2

Views: 2566

Answers (2)

TT.
TT.

Reputation: 16137

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.

Upvotes: 2

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14208

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

Upvotes: 2

Related Questions