如果object_id不为null，则无法删除过程

I have a weird problem. I'm trying to drop a procedure only if it exists, and have this code:

我有一个奇怪的问题。我只是尝试删除一个程序,如果它存在,并有这个代码:

IF OBJECT_ID(N'dbo.CreateBlogUser', N'U') IS NOT NULL
    DROP PROCEDURE CreateBlogUser;
    PRINT 'IS NOT NULL'
GO

(the print is only there to try if it is true or not). And when I run it, "IS NOT NULL" is printed, but the procedure isn't dropped! It still exists in the database, so when I run my Create procedure, it fails.

(只有在真实与否时才打印出来)。当我运行它时,会打印“IS NOT NULL”,但程序不会被删除!它仍然存在于数据库中,因此当我运行我的Create过程时,它会失败。

However! When I tried to remove the NOT from the code, it works! The procedure is dropped and "IS NOT NULL" is still printed. This seems totally backwards and I don't know why it does this. Is is something to do with the extra N:s and U:s in the OBJECT_ID? Found the code here

然而!当我试图从代码中删除NOT时,它可以工作!该过程被删除,仍然打印“IS NOT NULL”。这似乎完全倒退,我不知道为什么会这样做。是否与OBJECT_ID中的额外N:s和U:s有关?在这里找到了代码

2 个解决方案

#1

Replace 'U' on 'P'

替换'P'上的'U'

IF OBJECT_ID(N'dbo.CreateBlogUser', N'P') IS NOT NULL
  DROP PROCEDURE CreateBlogUser;
PRINT 'IS NOT NULL'
GO

#2

In SQL you can try this:-

在SQL中你可以试试这个: -

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'CreateBlogUser')
    exec('CREATE PROCEDURE [dbo].[CreateBlogUser] AS BEGIN SET NOCOUNT ON; END')
    PRINT 'IS NOT NULL'
 GO

In TSQL you can try this:-

在TSQL中你可以试试这个: -

 IF OBJECT_ID (N'dbo.CreateBlogUser', N'P') IS NOT NULL
  DROP TABLE dbo.CreateBlogUser;
  PRINT 'IS NOT NULL'
 GO

Refer this for documentation

请参阅此文档

#1