SQL 如果存储过程不存在，则创建它

Question

提问by Sean Smyth

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:

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

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:

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

Incorrect syntax near the keyword 'Procedure'.

关键字“Procedure”附近的语法不正确。

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

为什么我做的工作不正常？

Answer 1

回答by Code Magician

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

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

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

CREATE PROC dbo.procname

AS
....

    GO
    GRANT EXECUTE ON dbo.MyProc TO MyUser

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

（不要忘记 grant 语句，因为如果您重新创建 proc，它们将会丢失）

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 :)

部署存储过程时要考虑的另一件事是删除可以成功而创建失败。我总是在出现问题时回滚我的 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 = 'DatasetDeleteCleanup'
           AND type = 'P'
     )
COMMIT TRAN
ELSE
ROLLBACK TRAN
-- END DO NOT REMOVE THIS CODE

Answer 2

回答by Ben Thul

One idiom that I've been using lately that I like quite a lot is:

我最近一直在使用的一个我非常喜欢的成语是：

if exists (select 1 from sys.objects where object_id = object_id('dbo.yourProc'))
   set noexec on
go
create procedure dbo.yourProc as
begin
   select 1 as [not yet implemented]
end
go
set noexec off
alter procedure dbo.yourProc as
begin
   /*body of procedure here*/
end

Essentially, you're creating a stub if the procedure doesn't exist and then altering either the stub (if it was just created) or the pre-existing procedure. The nice thing about this is that you don't drop a pre-existing procedure which drops all the permissions as well. You can also cause issues with any application that happens to want it in that brief instant where it doesn't exist.

本质上，如果过程不存在，则您正在创建存根，然后更改存根（如果它刚刚创建）或预先存在的过程。这样做的好处是您不会删除一个预先存在的过程，该过程也会删除所有权限。您还可能导致任何应用程序在不存在的短暂瞬间出现问题。

[Edit 2018-02-09] - In SQL 2016 SP1, create procedureand drop proceduregot some syntactic sugar that helps with this kind of thing. Specifically, you can now do this:

[编辑2018年2月9日] -在SQL 2016 SP1，create procedure并drop procedure得到了一些语法糖，随着这种事情有帮助。具体来说，您现在可以这样做：

create or alter dbo.yourProc as
go

drop procedure if exists dbo.yourProc;

Both provide idempotency in the intended statement (i.e. you can run it multiple times and the desired state). This is how I'd do it now (assuming you're on a version of SQL Server that supports it).

两者都在预期的语句中提供幂等性（即您可以多次运行它并获得所需的状态）。这就是我现在的做法（假设您使用的是支持它的 SQL Server 版本）。

Answer 3

回答by Ron

I know that there's an accepted answer, but the answer does not address exactly what the original question asks, which is to CREATE the procedure if it does not exist. The following always works and has the benefit of not requiring dropping procedures which can be problematic if one is using sql authentication.

我知道有一个可接受的答案，但答案并没有完全解决原始问题所问的问题，即如果程序不存在则创建程序。以下始终有效，并且具有不需要删除过程的好处，如果使用 sql 身份验证，这可能会出现问题。

USE [MyDataBase]
GO

IF OBJECT_ID('mySchema.myProc') IS NULL
EXEC('CREATE PROCEDURE mySchema.myProc AS SET NOCOUNT ON;')
GO

ALTER PROCEDURE mySchema.myProc
    @DeclaredParmsGoHere    DataType

AS 
   BEGIN
       DECLARE @AnyVariablesINeed    Their DataType
   SELECT myColumn FROM myTable WHERE myIndex = @IndexParm

Answer 4

回答by Niladri

Just in case if you are using SQL server 2016, then there is a shorter version to check if the proc exist and then drop and recreate it

以防万一，如果您使用的是 SQL Server 2016，那么有一个较短的版本来检查 proc 是否存在，然后删除并重新创建它

USE [DATABASENAME]
GO
DROP PROCEDURE IF EXISTS <proc name>
GO
CREATE PROCEDURE <proc name>
AS
-- your script here
END
GO
GRANT EXECUTE ON <proc name> TO <username>

Source : https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

来源：https: //blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

Answer 5

回答by Ajay Dagade

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetRailItems]') AND type in (N'P', N'PC'))
BEGIN 
execute ('
CREATE PROCEDURE [dbo].[spGetRailItems]  
AS  
BEGIN  

Declare @isLiftedBagsEnable bit=1;  
select @isLiftedBagsEnable=cast(DataValu as bit) from setups where scope =''Rail Setting'' and dataName = ''isLiftedBagsEnable'';

IF @isLiftedBagsEnable=1
BEGIN
    IF EXISTS (SELECT * FROM ITEMCONFIG)
    BEGIN
        SELECT [Item],[Desc] FROM ProcData WHERE Item IN (SELECT Item FROM ItemConfig) ORDER BY [Desc]
    END
    ELSE
    BEGIN
        SELECT [Item],[Desc] FROM ProcData ORDER BY [Desc]
    END
END
ELSE
BEGIN
    SELECT [Item],[Desc] FROM ProcData ORDER BY [Desc]
END

END

')
END

exec spGetRailItems;

Answer 6

回答by KM.

I like to use ALTERso I don't lose permissions and if you have a syntax error the old version still exists:

我喜欢使用，ALTER所以我不会失去权限，如果您有语法错误，旧版本仍然存在：

BEGIN TRY
    --if procedure does not exist, create a simple version that the ALTER will replace.  if it does exist, the BEGIN CATCH will eliminate any error message or batch stoppage
    EXEC ('CREATE PROCEDURE AAAAAAAA AS DECLARE @A varchar(100); SET @A=ISNULL(OBJECT_NAME(@@PROCID), ''unknown'')+'' was not created!''; RAISERROR(@A,16,1);return 9999')
END TRY BEGIN CATCH END CATCH
GO

ALTER PROCEDURE AAAAAAAA 
(
     @ParamsHere varchar(10)
)
AS
PRINT 'HERE IN '+(OBJECT_NAME(@@PROCID))
GO

Answer 7

回答by Iskuskov Alexander

Updated on May 2020

2020 年 5 月更新

You can use CREATE OR ALTERstatement (was added in SQL Server 2016 SP1):

您可以使用CREATE OR ALTER语句（在 SQL Server 2016 SP1 中添加）：

The CREATE OR ALTERstatement acts like a normal CREATEstatement by creating the database object if the database object does not exist and works like a normal ALTERstatement if the database object already exists.

该CREATE OR ALTER声明的行为像一个正常CREATE通过创建数据库对象如果数据库对象不存在和作品像一个正常的声明ALTER语句，如果数据库对象已经存在。

Answer 8

回答by M.Ali

USE [myDatabase]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_1')
BEGIN
  DROP PROCEDURE sp_1
END
GO   --<-- Add a Batch Separator here



CREATE PROCEDURE sp_1
AS
.................
END
GO

Answer 9

回答by jdlcgarcia

you can execute the following:

您可以执行以下操作：

DROP PROCEDURE IF EXISTS name_of_procedure;
CREATE PROCEDURE name_of_procedure(....)

SQL 如果存储过程不存在，则创建它

提问by Sean Smyth

回答by Code Magician

回答by Ben Thul

回答by Ron

回答by Niladri

回答by Ajay Dagade

回答by KM.

回答by Iskuskov Alexander

回答by M.Ali

回答by jdlcgarcia

相关推荐

最近更新

标签

SQL 如果存储过程不存在，则创建它

提问by Sean Smyth

回答by Code Magician

回答by Ben Thul

回答by Ron

回答by Niladri

回答by Ajay Dagade

回答by KM.

回答by Iskuskov Alexander

回答by M.Ali

回答by jdlcgarcia

相关推荐

SQL 当列已为 NOT NULL 时，无法在可空列上定义 PRIMARY KEY 约束

如何将一个字段中的日期与另一个字段中的时间相结合 - MS SQL Server

SQL 如何在 MSSQL 中声明 VARCHAR 变量

如何使用 SQL Server 2005 将逗号分隔值扩展为单独的行？

相关推荐

最近更新

标签