删除表的存储过程 [英] Stored procedure to drop table
问题描述
我创建了一个存储过程,如果它存在于数据库中,它将删除一个表.使用 EXEC 运行存储过程时,出现以下错误:
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:
消息 203,级别 16,状态 2,程序sp_DropIfExists,第 13 行名称 'IF存在(从 sys.objects 中选择 1OBJECT_ID = OBJECT_ID(N'table_name')AND type = (N'U')) 删除表[table_name]' 无效标识符.
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.
但是,如果我将生成的 T-SQL 复制并粘贴到 management studio 中,它似乎运行良好.有人可以解释为什么这是无效的吗?修复会很好,但我真的主要是在追求为什么,尽管如此会很好!提前致谢.
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.
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
推荐答案
你可以使用sp_execute
sp_executesql @SQL
有关详细信息msdn 文档链接
这篇关于删除表的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!