如何在创建存储过程前检查存储过程是否存在

我有一个SQL脚本，每次客户端执行“数据库管理”功能时都必须运行。该脚本包括在客户端数据库上创建存储过程。其中一些客户端在运行脚本时可能已经拥有存储过程，而另一些客户端可能没有。我需要将丢失的存储过程添加到客户端数据库中，但无论我如何尝试改变T-SQL语法，我都得到了这个结果

CREATE/ALTER PROCEDURE'必须是查询批处理中的第一个语句

我在创作作品之前读到过这种说法，但我不喜欢这样做。

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
DROP PROCEDURE MyProc
GO


CREATE PROCEDURE MyProc
...

如何添加检查存储过程是否存在，如果存储过程不存在则创建它，如果存储过程存在则修改它?

650552 次浏览
小开
最佳答案

您可以在任何能够运行查询的地方运行过程代码。

只需要复制AS之后的所有内容:

BEGIN
DECLARE @myvar INT
SELECT  *
FROM    mytable
WHERE   @myvar ...
END

这段代码所做的事情与存储的proc完全相同，但是没有存储在数据库端。

这很像PL/SQL中所谓的匿名过程。

更新:

你的题目有点让人困惑。

如果您只需要在过程不存在的情况下创建它，那么您的代码就可以了。

下面是SSMS在创建脚本中输出的内容:

IF EXISTS ( SELECT  *
FROM    sys.objects
WHERE   object_id = OBJECT_ID(N'myproc')
AND type IN ( N'P', N'PC' ) )
DROP …
CREATE …

更新:

当包含模式时如何做的例子:

IF EXISTS ( SELECT *
FROM   sysobjects
WHERE  id = object_id(N'[dbo].[MyProc]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE [dbo].[MyProc]
END

在上面的例子中，dbo是模式。

更新:

在SQL Server 2016+中，您可以这样做

CREATE OR ALTER PROCEDURE dbo.MyProc

小开

我意识到这已经被标记为已回答，但我们过去是这样做的:

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


ALTER PROCEDURE [dbo].[MyProc]
AS
....

只是为了避免手术失败。

小开

如果你正在寻找最简单的方法来检查数据库对象是否存在，然后再删除它，这里有一种方法(示例使用SPROC，就像上面的例子一样，但可以修改表，索引等…):

IF (OBJECT_ID('MyProcedure') IS NOT NULL)
DROP PROCEDURE MyProcedure
GO

这是快速和优雅的，但您需要确保在所有对象类型中都有唯一的对象名称，因为它不会考虑这一点。

我希望这能有所帮助!

小开

我也犯了同样的错误。我知道这个线程已经死了，但我想设置另一个选项除了“匿名过程”。

我是这样解决的:

  1. 检查存储过程是否存在。

    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='my_procedure') BEGIN
print 'exists'  -- or watever you want
END ELSE BEGIN
print 'doesn''texists'   -- or watever you want
END

  2. However the "CREATE/ALTER PROCEDURE' must be the first statement in a query batch" is still there. I solved it like this:

    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE -- view procedure function or anything you want ...

  3. I end up with this code:

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('my_procedure'))
BEGIN
DROP PROCEDURE my_procedure
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].my_procedure ...
小开

你为什么不简单点

    IF EXISTS(SELECT * FROM sys.procedures WHERE NAME LIKE 'uspBlackListGetAll')
BEGIN
DROP PROCEDURE uspBlackListGetAll
END
GO


CREATE Procedure uspBlackListGetAll

..........

小开

这里有一个方法和一些使用它的理由。编辑存储的proc并不是很漂亮，但是有利有弊……

更新:您还可以将整个调用包装在一个TRANSACTION中。在单个事务中包含多个存储过程，这些存储过程可以全部提交或全部回滚。在事务中封装的另一个优点是，只要其他SQL连接不使用READ UNCOMMITTED事务隔离级别，存储过程就始终存在!

1)避免将变更作为流程决策。我们的过程总是IF存在，然后创建。如果您执行相同的模式，假设新的PROC是所需的PROC，那么满足alters就有点困难，因为您将拥有If EXISTS ALTER ELSE CREATE。

2)你必须把CREATE/ALTER作为批处理中的第一个调用，这样你就不能在动态SQL之外的事务中包装一系列过程更新。基本上，如果您想运行整个过程更新堆栈或在不恢复DB备份的情况下将它们全部回滚，这是一种在单个批处理中完成所有工作的方法。

IF NOT EXISTS (select ss.name as SchemaName, sp.name as StoredProc
from sys.procedures sp
join sys.schemas ss on sp.schema_id = ss.schema_id
where ss.name = 'dbo' and sp.name = 'MyStoredProc')
BEGIN
DECLARE @sql NVARCHAR(MAX)


-- Not so aesthetically pleasing part. The actual proc definition is stored
-- in our variable and then executed.
SELECT @sql = 'CREATE PROCEDURE [dbo].[MyStoredProc]
(
@MyParam int
)
AS
SELECT @MyParam'
EXEC sp_executesql @sql
END
小开

创建程序(如果不存在)结束

小开

在T-Sql中删除并重新创建存储的proc的最简单方法是**

Use DatabaseName
go
If Object_Id('schema.storedprocname') is not null
begin
drop procedure schema.storedprocname
end
go


create procedure schema.storedprocname
as


begin
end
小开

除了@Geoff的答案之外，我还创建了一个简单的工具，它可以生成一个sql文件，其中包含存储过程、视图、函数和触发器的语句。

< p >看到MyDbUtils @ CodePlex。 enter image description here < / p >
小开

我想知道!为什么我不写整个查询像这样

GO
create procedure [dbo].[spAddNewClass] @ClassName varchar(20),@ClassFee int
as
begin
insert into tblClass values (@ClassName,@ClassFee)
end


GO
create procedure [dbo].[spAddNewSection] @SectionName varchar(20),@ClassID       int
as
begin
insert into tblSection values(@SectionName,@ClassID)
end


Go
create procedure test
as
begin
select * from tblstudent
end

我已经知道前两个过程已经存在，SQL将运行查询将给出前两个过程的错误，但它仍然会创建最后一个过程 SQl本身是照顾什么是已经存在的，这是我总是做我的客户端!< / p >

小开

在Sql server 2008以后，你可以使用"INFORMATION_SCHEMA.ROUTINES"

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MySP'
AND ROUTINE_TYPE = 'PROCEDURE')
小开

从SQL SERVER 2016开始，你可以使用新的DROP PROCEDURE IF EXISTS.
. 0 DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ] < / p > < p >参考: https://msdn.microsoft.com/en-us/library/ms174969.aspx < / p >

小开

我知道这是一个非常老的帖子，但由于这出现在顶部搜索结果中，因此为那些使用SQL Server 2016 SP1 -的人添加了最新更新

create or alter procedure procTest
as
begin
print (1)
end;
go

如果存储过程不存在，则创建存储过程;如果存储过程存在，则更改存储过程。

Reference .

小开

我知道你想“改变一个存在的过程，创建一个不存在的过程”，但我相信这样更简单:

  1. 删除该过程(如果它已经存在)，然后
  2. 重新创建它。

是这样的:

IF OBJECT_ID('MyProcedure', 'P') IS NOT NULL
DROP PROCEDURE MyProcedure
GO


CREATE PROCEDURE MyProcedure AS
BEGIN
/* ..... */
END
GO

第二个参数告诉OBJECT_ID只查找带有object_type = 'P'， 它们是存储过程:的对象

AF =聚合函数(CLR)

C = CHECK约束

D = DEFAULT(约束或独立)

F =外键约束

FN = SQL标量函数

FS =汇编(CLR)标量函数

FT =汇编(CLR)表值函数

IF = SQL内联表值函数

IT =内部表

SQL存储过程

PC =组装(CLR)存储过程

规划指南

PK =主键约束

R =规则(老式的，独立的)

RF =复制-过滤器-过程

S =系统基表

SN =同义词

SO =序列对象

TF = SQL表值函数

TR =触发

你可以通过以下途径获得完整的选项列表:

SELECT name
FROM master..spt_values
WHERE type = 'O9T'
小开

下面是我使用的脚本。使用它，我可以避免不必要地删除和重新创建存储的过程。

IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspMyProcedure]')
)
BEGIN
EXEC sp_executesql N'CREATE PROCEDURE [dbo].[uspMyProcedure] AS select 1'
END
GO


ALTER PROCEDURE [dbo].[uspMyProcedure]
@variable1 INTEGER
AS
BEGIN
-- Stored procedure logic
END
小开

DROP IF EXISTS 是SQL Server 2016的新特性

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

DROP  PROCEDURE IF EXISTS dbo.[procname]

提交答案