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, Oct 13 '17 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, Oct 13 '17 at 20:16

score 2 · Answer 1 · answered Oct 13 '17 at 19:38

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

Pass table name using parameter in SQL Server stored procedure

1 Answers1