如何检测存储过程是否已经存在

我必须编写一个部署脚本，如果存储过程存在或不存在，这个脚本将会工作。也就是说，如果它存在，那么我需要改变它，否则就创建它。

我怎么才能在 sql 中做到这一点。

我正在使用 SQLServer2005

218587 次浏览
小开 
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
CREATE PROCEDURE dbo.xxx

其中的 xxx是程序名称

小开

一个更好的选择可能是使用 Red-Gate SQL Compare 或 SQL Examiner 这样的工具来自动比较差异并生成迁移脚本。

小开 
IF OBJECT_ID('SPNAME') IS NULL
-- Does Not Exists
ELSE
-- Exists
小开

最干净的方法是测试它是否存在，如果它存在就丢弃它，然后重新创建它。不能在 IF 语句中嵌入“ create proc”语句。这样应该很好:

IF OBJECT_ID('MySproc', 'P') IS NOT NULL
DROP PROC MySproc
GO


CREATE PROC MySproc
AS
BEGIN
...
END
小开

如果您只处理存储过程，那么最简单的做法可能是删除该过程，然后重新创建它。可以使用 SQLServer 中的“生成脚本”向导生成完成此操作的所有代码。

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourSproc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[YourSproc]


CREATE PROCEDURE YourSproc...
小开

如果删除和创建过程，则会失去安全设置。这可能会惹恼您的 DBA，或者完全破坏您的应用程序。

我所做的就是创建一个不重要的存储过程，如果它还不存在的话。之后，您可以根据自己的喜好更改存储过程。

IF object_id('YourSp') IS NULL
EXEC ('create procedure dbo.YourSp as select 1')
GO
ALTER PROCEDURE dbo.YourSp
AS
...

这样，安全设置、注释和其他元数据将在部署过程中存活下来。

小开

除了已经说过的内容之外，我还想添加一种不同的方法，并提倡使用差异脚本部署策略。与其创建一个总是检查当前状态并根据该状态执行操作的有状态脚本，不如通过一系列从已知版本升级的无状态脚本进行部署.我已经使用了这个策略，它回报很大，因为我的部署脚本现在都是“ IF”免费的。

小开

我有一个存储的过程，允许客户扩展验证，如果它存在，我不想改变它，如果它不，我想创建它，最好的方式，我发现:

IF OBJECT_ID('ValidateRequestPost') IS NULL
BEGIN
EXEC ('CREATE PROCEDURE ValidateRequestPost
@RequestNo VARCHAR(30),
@ErrorStates VARCHAR(255) OUTPUT
AS
BEGIN
SELECT @ErrorStates = @ErrorStates
END')
END
小开

从 SQL Server 2016 CTP3可以使用新的 去死吧语句而不是大的 IF包装器

句法:

DROP { PROC | PROCEDURE }[ IF EXISTS ]{[ schema _ name. ] process } [ ，... n ]

质疑:

DROP PROCEDURE IF EXISTS usp_name

更多信息 这里

小开

下面的代码将检查存储过程是否已经存在。

如果它存在，它会改变，如果它不存在，它会为你创建一个新的存储过程:

//syntax for Create and Alter Proc
DECLARE @Create NVARCHAR(200) = 'Create PROCEDURE sp_cp_test';
DECLARE @Alter NVARCHAR(200) ='Alter PROCEDURE sp_cp_test';
//Actual Procedure
DECLARE @Proc NVARCHAR(200)= ' AS BEGIN select ''sh'' END';
//Checking For Sp
IF EXISTS (SELECT *
FROM   sysobjects
WHERE  id = Object_id('[dbo].[sp_cp_test]')
AND Objectproperty(id, 'IsProcedure') = 1
AND xtype = 'p'
AND NAME = 'sp_cp_test')
BEGIN
SET @Proc=@Alter + @Proc


EXEC (@proc)
END
ELSE
BEGIN
SET @Proc=@Create + @Proc


EXEC (@proc)
END


go
小开

您可以编写如下查询:

IF OBJECT_ID('ProcedureName','P') IS NOT NULL
DROP PROC ProcedureName
GO


CREATE PROCEDURE [dbo].[ProcedureName]
...your query here....

更详细地说明以上语法:
OBJECT _ ID 是数据库中对象的唯一 id 号，SQL Server 在内部使用这个 ID 号。因为我们传递的是 程序名称，然后是 P对象类型，它告诉 SQL Server 您应该找到 程序名称对象，它的类型是 程序，即 P

这个查询将找到这个过程，如果它可用，它将删除它并创建一个新的过程。

有关 OBJECT _ ID 和对象类型的详细信息，请访问: SYS.Objects < a href = “ https://Learn.microsoft.com/en-us/sql/闽系统数据库/system-catalog-views/sys-Objects-transact-sql? view = sql-server-2017”rel = “ norefrer”> SYS.Objects


提交答案