Pass table name using parameter in SQL Server stored procedure

Question

I want to create a stored procedure that will update a table. The procedure will join two tables and I want to pass the table name using a variable (@tablename).

This error is generated:

Must declare the table variable "@tablename".

My code:

Create Procedure dbo.SpUpdate (@TableName varchar(50))
as
begin
    set @tablename='Customer'

    Update a
    Set AgentNumber = '5',
    From dbo.CustomerList a
    join @tablename b on a.customerid = b.customerid
end

Update question to include what database you are on. Pretty sure you'll be forced to dynamic sql here...though I'd suggest you ask why you are doing it this way and if there is a better way of achieving what you want here. — Twelfth
– Twelfth, Commented Oct 13, 2017 at 19:35
In addition to the fixes below you have some other issues here. You have a comma after the first line in the update but you are only updating a single column. And if you have a case sensitive collation this will fail because TableName <> tablename — Sean Lange
– Sean Lange, Commented Oct 13, 2017 at 20:16

Serkan Arslan · Accepted Answer · 2017-10-13 19:38:11Z

2

You can use this script:

Create Procedure dbo.SpUpdate (@TableName varchar(50))
as
begin

DECLARE @SqlText NVARCHAR(MAX)

SET @SqlText = 
'Update a
Set AgentNumber=''5'',
From dbo.CustomerList a
join ' + QUOTENAME(@tablename) + ' b
on a.customerid= b.customerid'

EXEC sp_executesql @SqlText

end

answered Oct 13, 2017 at 19:38

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pass table name using parameter in SQL Server stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related