如何对丢弃表的T-SQL存储过程进行参数化？

I'm after a simple stored procedure to drop tables. Here's my first attempt:

我正在使用一个简单的存储过程来删除表。这是我的第一次尝试:

CREATE PROC bsp_susf_DeleteTable (@TableName char)
AS
IF EXISTS (SELECT name FROM sysobjects WHERE name = @TableName)
BEGIN
DROP TABLE @TableName
END

When I parse this in MS Query Analyser I get the following error:

当我在MS查询分析器中解析它时,我收到以下错误:

Server: Msg 170, Level 15, State 1, Procedure bsp_susf_DeleteTable, Line 6
Line 6: Incorrect syntax near '@TableName'.

Which kind of makes sense because the normal SQL for a single table would be:

哪种有意义,因为单个表的普通SQL将是:

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tbl_XYZ')
BEGIN
    DROP TABLE tbl_XYZ
END

Note the first instance of tbl_XYZ (in the WHERE clause) has single quotes around it, while the second instance in the DROP statement does not. If I use a variable (@TableName) then I don't get to make this distinction.

请注意,tbl_XYZ的第一个实例(在WHERE子句中)周围有单引号,而DROP语句中的第二个实例则没有。如果我使用变量(@TableName),那么我无法做出这种区分。

So can a stored procedure be created to do this? Or do I have to copy the IF EXISTS ... everywhere?

那么可以创建存储过程来执行此操作吗?或者我是否必须复制IF EXISTS ......到处都是?

3 个解决方案

#1

You should be able to use dynamic sql:

你应该能够使用动态sql:

declare @sql varchar(max)
if exists (select name from sysobjects where name = @TableName)
BEGIN
   set @sql = 'drop table ' + @TableName
   exec(@sql)
END

Hope this helps.

希望这可以帮助。

Update: Yes, you could make @sql smaller, this was just a quick example. Also note other comments about SQL Injection Attacks

更新:是的,你可以让@sql更小,这只是一个简单的例子。另请注意有关SQL注入攻击的其他注释

#2

Personally I would be very wary of doing this. If you feel you need it for administrative purposes, please make sure the rights to execute this are extremely limited. Further, I would have the proc copy the table name and the date and the user executing it to a logging table. That way at least you will know who dropped the wrong table. You may want other protections as well. For instance you may want to specify certain tables that cannot be dropped ever using this proc.

就个人而言,我会非常谨慎地这样做。如果您认为出于管理目的需要它,请确保执行此操作的权限非常有限。此外,我将proc复制表名和日期以及执行它的用户到日志表。这样至少你会知道是谁丢错了桌子。您可能还需要其他保护措施。例如,您可能希望指定使用此proc无法删除的某些表。

Further this will not work on all tables in all cases. You cannot drop a table that has a foreign key associated with it.

此外,在所有情况下,这都不适用于所有表格。您不能删除具有与之关联的外键的表。

Under no circumstances would I allow a user or anyone not the database admin to execute this proc. If you havea a system design where users can drop tables, there is most likely something drastically wrong with your design and it should be rethought.

在任何情况下,我都不允许用户或不是数据库管理员的任何人执行此proc。如果你有一个系统设计,用户可以放弃表格,你的设计很可能会出现严重错误,应该重新考虑。

Also, do not use this proc unless you have a really, really good backup schedule in place and experience restoring from backups.

此外,请不要使用此proc,除非您有一个非常非常好的备份计划并体验从备份恢复。

#3

You'll have to use EXEC to execute that query as a string. In other words, when you pass in the table name, define a varchar and assign the query and tablename, then exec the variable you created.

您必须使用EXEC以字符串形式执行该查询。换句话说,当您传入表名时,定义varchar并分配查询和表名,然后执行您创建的变量。

Edit: HOWEVER, I don't recommend that because someone could pass in sql rather than a TableName and cause all kinds of wonderful problems. See Sql injection for more information.

编辑:但是,我不建议,因为有人可以传入sql而不是TableName,并导致各种奇妙的问题。有关更多信息,请参阅Sql注入。

Your best bet is to create a parameterized query on the client side for this. For example, in C# I would do something like:

最好的办法是在客户端为此创建一个参数化查询。例如,在C#中我会做类似的事情:

// EDIT 2: on second thought, ignore this code; it probably won't work
SqlCommand sc = new SqlCommand();
sc.Connection = someConnection;
sc.CommandType = Command.Text;
sc.CommandText = "drop table @tablename";
sc.Parameters.AddWithValue("@tablename", "the_table_name");
sc.ExecuteNonQuery();

#1