删除数据库中的存储过程

When checking for temptables (and then deleting them), I'm using a similar format to this:

在检查temptables(然后删除它们)时，我使用了类似的格式:

if object_id('my_sp', 'U') is not null drop procedure my_sp
go

create procedure dbo.my_sp (
    @query varchar(100)
  , @result varchar(100) 
) as
insert into [TABLE] (finished, query, result) 
values (getdate(), @query, @result)
go

Looking at the examples on MSDN, this approach should work. However, it doesn't:

看看MSDN上的示例，这种方法应该是有效的。然而,它不:

There is already an object named 'my_sp' in the database.

数据库中已经有一个名为“my_sp”的对象。

An alternative approach would be something like

另一种方法是类似的。

SELECT
    OBJECT_NAME(my_sp)
FROM
    sys.sql_modules
WHERE
    definition LIKE '%' + 'mySP' + '%'

But then the question remains: why does the first approach work for (temp)tables but not for sp objects? (I like to keep things consistent).

但是问题仍然存在:为什么第一种方法适用于(临时)表而不适用于sp对象?(我喜欢保持一致)。

1 个解决方案

#1

The U in object_id('my_sp', 'U') specifies the name is a user table, use P for a procedure: object_id('my_sp', 'P')

object_id中的U ('my_sp'， 'U')指定名称是一个用户表，对一个过程使用P: object_id('my_sp'， 'P')