如果存储过程不存在，则创建它 [英] Creating a stored procedure if it does not already exist

问题描述

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

I want to check if a list of stored procedures exist. I want this all to be done in 1 script, one by one. So far I have this format:

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

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

and so on. However, I'm getting the following error:

关键字Procedure"附近的语法不正确.

Incorrect syntax near the keyword 'Procedure'.

为什么我所做的工作不正常?

Why isn't what I'm doing working correctly?

推荐答案

CREATE PROCEDURE 必须是批处理中的第一条语句.我通常这样做:

CREATE PROCEDURE must be the first statement in the batch. I usually do something like this:

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 语句，因为如果您重新创建 proc，它们将会丢失)

(don't forget grant statements since they'll be lost if you recreate your proc)

在部署存储过程时要考虑的另一件事是删除可以成功而创建失败.我总是在出现问题时回滚我的 SQL 脚本.只要确保你最后没有不小心删除提交/回滚代码，否则你的 DBA 可能会把你踢到气管里:)

One other thing to consider when you are deploying stored procedures is that a drop can succeed and a create fail. I always write my SQL scripts with a rollback in the event of a problem. Just make sure you don't accidentally delete the commit/rollback code at the end, otherwise your DBA might crane-kick you in the trachea :)

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

这篇关于如果存储过程不存在，则创建它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！