给 DROP PROCEDURE 添加参数

Question

我第一次使用SqlServer，在我们的每个创建过程脚本中都有以下代码块，用于删除已存在的过程：

IF EXISTS (SELECT *
           FROM information_schema.routines
           WHERE routine_name = 'SomeProcedureName'
           AND routine_type = 'PROCEDURE'

BEGIN
    DROP PROCEDURE SomeProcedureName
END
//then the procedure definition

为了避免在每个文件中都复制和粘贴此样板代码，我想将此代码放入自己的存储过程中，这样脚本看起来会像这样：

DropIfRequired('SomeProcedureName')
//then the procedure definition

我尝试的解决方案是：

CREATE PROCEDURE DropIfRequired
(
    @procedureName varchar
)
AS
IF EXISTS (SELECT * FROM information_schema.routines 
           WHERE routine_name = @procedureName 
           AND routine_type = 'PROCEDURE') 
BEGIN
    DROP PROCEDURE @procedureName
END

但是我接下来遇到了以下错误：

Msg 102，级别 15，状态 1，过程 DeleteProcedure，行 10 @procedureName 附近有语法错误。

有什么办法可以实现我想要的效果吗？

- Argos

---

点击这个答案获取更多解决方案。 - Sam

Answer 1

完整的答案如下:

DECLARE @SQL VARCHAR(8000)
SELECT @SQL = 'USE ' + DB_NAME() + CHAR(10)
SET @SQL = @SQL + 'DROP PROCEDURE ' + @procName
--PRINT @SQL
EXEC(@SQL)

Andrew提供的答案只有当您的登录默认数据库设置为所需的数据库时才能起作用。使用动态SQL会获得一个新的数据库上下文。所以如果您没有设置默认数据库，您将从master执行命令。

- Cervo

---

是的，没错。我以前也因为那个疏忽而吃过亏。 - StingyJack

Answer 2

需要注意的一点是，在DropIfRequired过程中，你已经定义了以下过程名称：

CREATE PROCEDURE DropIfRequired
(    
   @procedureName varchar
)

你需要定义varchar参数的长度，否则SQL将假定长度为一个字符。相反，应该像下面这样做（1000对于大多数过程名称来说应该足够了）

CREATE PROCEDURE DropIfRequired
(    
   @procedureName varchar(1000)
)

- Tim C

---

您还可以使用数据类型sysname（相当于nvarchar（128）），它可以容纳任何有效的对象名称。 - GilM

"1000应该足够大多数过程名称使用了"...我不敢想象例外情况。 - user1919238

Answer 3

缺少引号，请在exec语句中添加引号。

EXEC( 'DROP PROCEDURE ''' + @procName + '''') ( all single quotes)

- Andrew

---

也许更好的方法是 exec('DROP PROCEDURE [' + @procName + ']')。 - abatishchev