SQL 删除表的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6046510/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored procedure to drop table
提问by SSISPissesMeOff
I have created a stored procedure that will drop a table if it exists in a database. When running the stored procedure with EXEC, I am getting the following error:
我创建了一个存储过程，如果它存在于数据库中，它将删除一个表。使用 EXEC 运行存储过程时，出现以下错误：
Msg 203, Level 16, State 2, Procedure sp_DropIfExists, Line 13 The name 'IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'table_name') AND type = (N'U')) DROP TABLE [table_name]' is not a valid identifier.
消息 203，级别 16，状态 2，过程 sp_DropIfExists，第 13 行名称 'IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'table_name') AND type = (N'U')) DROP TABLE [table_name ]' 不是有效的标识符。
However if i copy and paste the T-SQL that is generated into management studio, it seems to be running fine. Can someone explain why this is not valid? The fix would be nice, but I am really after the Why primarily, The How would be nice to though! Thanks in advance.
但是，如果我将生成的 T-SQL 复制并粘贴到管理工作室中，它似乎运行良好。有人可以解释为什么这是无效的吗？修复会很好，但我真的主要是在追求为什么，尽管如此会很好！提前致谢。
ALTER PROCEDURE [dbo].[sp_DropIfExists](@tableName VARCHAR(255))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(MAX);
SET @SQL = 'IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N''' + @tableName + ''') AND type = (N''U'')) DROP TABLE [' + @tableName + ']'
PRINT @SQL;
EXEC @SQL;
END
回答by maycil
you can use sp_execute
您可以使用 sp_execute
sp_executesql @SQL
for more information msdn document link
有关更多信息，请访问msdn 文档链接
回答by Ash Burlaczenko
Not sure if this will solve your problems but you would be better placing you check is a function like so
不确定这是否会解决您的问题，但您最好将检查是这样的功能
CREATE FUNCTION [dbo].[TableExists]
(
@TableName VarChar(100)
)
RETURNS BIT
AS
BEGIN
DECLARE @TableExists BIT
IF EXISTS(SELECT name FROM sysobjects a
WHERE a.name = @TableName
AND a.xtype = 'U')
SET @TableExists = 1
ELSE
SET @TableExists = 0
RETURN @TableExists
END
Then you can use it as follows.
然后您可以按如下方式使用它。
IF dbo.TableExists('[table_name]') = 1
DROP TABLE [table_name]
Try this and let me know if you still get the same error.
试试这个，如果你仍然遇到同样的错误，请告诉我。
回答by solycon
--ALTER (if procedure exists)
CREATE PROCEDURE sp_dropifexists (@tableName VARCHAR(255))
AS
BEGIN
DECLARE @SQL VARCHAR(MAX);
SET @SQL = 'IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N''' + @tableName + ''') AND type = (N''U'')) DROP TABLE [' + @tableName + ']'
--if write EXEC @SQL without parentheses sql says Error: is not a valid identifier
EXEC (@SQL);
END
--test procedure
exec sp_DropIfExists 'table'
回答by Andriy M
EXEC @SQLshould be EXEC (@SQL). (But @maycil's suggestion is correct too.)
EXEC @SQL应该是EXEC (@SQL)。（但@maycil的建议也是正确的。）
Turns out, without the parentheses @SQL's value is interpreted as the name of a stored procedure to execute, not as a script. (I didn't know that before, but I made a small test to verify that it is indeed so.)
事实证明，没有括号@SQL的值被解释为要执行的存储过程的名称，而不是脚本。（我之前不知道，但我做了一个小测试来验证确实如此。）
