存储过程中的表变量

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'.

无效的对象名称'dbo。@ TableName'。

Any advice?

1 个解决方案

#1

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.

注意:您没有WHERE子句,因此@TableName中的所有项都将更新。

#1