Table variable in a stored procedure

Question

I would like to create a simple stored procedure which take as a parameter existing tables.

I thought this procedure should work:

@UserID INT,
@TableName varchar(255)
AS
BEGIN
    IF(@UserID is not null)
    BEGIN
       update t
       set t.ProductID = 100
       from dbo.[@TableName] t

    END

When I execute this stored procedure with a table name, the query completed with errors:

Invalid object name 'dbo.@TableName'.

Any advice?

you can't do query a dynamic table name like that. Would need to use dynamic TSQL — Mitch Wheat
possible duplicate of Stored procedure, pass table name as a parameter — Pondlife

Darren Darren · Accepted Answer · 2013-05-14T10:48:04

You'd have to do something like the following:

DECLARE @SQL NVARCHAR(100)
SET @SQL = 'UPDATE ' + @TABLENAME + ' SET t.ProductID = 100 '
EXEC sp_executesql @SQL

Note: You have no WHERE clause so all items in the @TableName will be updated.

Table variable in a stored procedure

1 Answers