sql - 创建存储过程(如果尚不存在)

Question

我想检查存储过程列表是否存在。我希望这一切都在一个脚本中一一完成。到目前为止我有这样的格式:

USE [myDatabase]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_1')
BEGIN
CREATE PROCEDURE sp_1
AS
.................
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_2')
BEGIN
CREATE PROCEDURE sp_2
AS
.................
END
GO

等等。但是，我收到以下错误:

Incorrect syntax near the keyword 'Procedure'.

为什么我正在做的事情不能正常工作？

Answer 1

CREATE PROCEDURE 必须是批处理中的第一个语句。我通常会做这样的事情:

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'myProc'
            AND type = 'P'
      )
     DROP PROCEDURE dbo.myProc
GO

CREATE PROC dbo.myProc

AS
....

    GO
    GRANT EXECUTE ON dbo.myProc TO MyUser 

(不要忘记 grant 语句，因为如果您重新创建过程，它们就会丢失)

部署存储过程时要考虑的另一件事是删除可能会成功而创建会失败。我总是编写 SQL 脚本，并在出现问题时进行回滚。只要确保你没有意外删除最后的提交/回滚代码，否则你的 DBA 可能会用起重机踢你的气管:)

BEGIN TRAN 
IF EXISTS (
       SELECT type_desc, type
       FROM sys.procedures WITH(NOLOCK)
       WHERE NAME = 'myProc'
           AND type = 'P'
     )
DROP PROCEDURE myProc GO
CREATE PROCEDURE myProc
   
AS
   --proc logic here

GO
-- BEGIN DO NOT REMOVE THIS CODE (it commits or rolls back the stored procedure drop) 
    IF EXISTS(
               SELECT 1
               FROM sys.procedures WITH(NOLOCK)
               WHERE NAME = 'myProc'
                   AND type = 'P'
             )
        COMMIT TRAN
        ELSE
        ROLLBACK TRAN
-- END DO NOT REMOVE THIS CODE