blocks|key|4297365|text|您必须使用EXEC将该查询作为字符串执行。换句话说，当您传入表名时，定义一个varchar并分配query和tablename，然后执行您创建的变量。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4297366|编辑:但是，我不建议这样做，因为有人可能会传入sql而不是TableName，从而导致各种奇妙的问题。有关详细信息，请参阅Sql注入。|4297367|最好的办法是在客户端为此创建一个参数化查询。例如，在C#中，我会这样做：|4297368|//+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();|code-block|syntax|javascript|4297369|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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.

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.

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

<pre><code>// 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();
</code></pre>

blocks|key|4297409|text|您应该能够使用动态sql：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4297410|declare+@sql+varchar(max)
if+exists+(select+name+from+sysobjects+where+name+=+@TableName)
BEGIN
+++set+@sql+=+'drop+table+'+%2B+@TableName
+++exec(@sql)
END|code-block|syntax|javascript|4297411|希望这能有所帮助。|4297412|更新:是的，你可以让@sql变得更小，这只是一个简单的例子。还要注意有关SQL注入攻击的其他注释|4297413|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You should be able to use dynamic sql:

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

Hope this helps.

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

blocks|key|103792|text|就我个人而言，我会非常谨慎地这样做。如果您觉得您需要它用于管理目的，请确保执行此命令的权限非常有限。此外，我会让proc将表名和日期以及执行它的用户复制到一个日志表中。这样，您至少可以知道是谁弄错了表。你可能还需要其他的保护措施。例如，您可能希望指定使用此过程永远不能删除的某些表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|103793|此外，这并不是在所有情况下都适用于所有表。不能删除具有关联外键的表。|103794|在任何情况下，我都不会允许用户或数据库管理员以外的任何人执行此过程。如果你有一个用户可以删除表的系统设计，你的设计很可能有严重的错误，应该重新考虑。|103795|此外，请不要使用此过程，除非您有一个非常非常好的备份计划，并且有从备份恢复的经验。|103796|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

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. 

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.

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

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

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

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

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

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

<pre><code>IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tbl_XYZ')
BEGIN
 DROP TABLE tbl_XYZ
END
</code></pre>

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.

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

How do I paramaterise a T-SQL stored procedure that drops a table?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我想要一个简单的存储过程来删除表。这是我的第一次尝试：CREATE PROC bsp_susf_DeleteTable (@TableName char)ASIF EXISTS (SELECT name FROM sysobjects WHERE name = @TableName)BEGINDROP TABLE @T...

问如何参数化删除表的T-SQL存储过程？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何参数化删除表的T-SQL存储过程？EN