如何在创建存储过程之前检查存储过程是否存在-Java 学习之路

221

我有一个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
...

如何添加检查是否存在存储过程并创建它（如果它不存在）但是如果它存在则更改它？

18 回答

0
您可以在能够运行查询的任何位置运行过程代码 .

只需复制 AS 之后的所有内容：
```
BEGIN
    DECLARE @myvar INT
    SELECT  *
    FROM    mytable
    WHERE   @myvar ...
END
```
此代码与存储过程执行的操作完全相同，但不存储在数据库端 .

这与 PL/SQL 中所谓的匿名程序非常相似 .

Update:

你的问题 Headers 有点令人困惑 .

如果您只需要创建一个不存在的过程，那么您的代码就可以了 .

这是创建脚本中 SSMS 输出的内容：
```
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'myproc')
                    AND type IN ( N'P', N'PC' ) ) 
DROP …
CREATE …
```
Update:

包含架构时如何执行此操作的示例：
```
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是架构 .

Update:

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

CREATE OR ALTER PROCEDURE dbo.MyProc
回复于 2024-04-19T12:26:08+08:00

我意识到这已被标记为已回答，但我们过去常常这样做：

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
  ....

只是为了避免丢弃程序 .

回复于 2024-04-19T12:26:08+08:00

1
如果您正在寻找在删除数据库对象之前检查数据库对象的最简单方法，那么这是一种方式（示例使用SPROC，就像上面的示例一样，但可以针对表，索引等进行修改）：
```
IF (OBJECT_ID('MyProcedure') IS NOT NULL)
  DROP PROCEDURE MyProcedure
GO
```
这是快速而优雅的，但您需要确保在所有对象类型中都有唯一的对象名称，因为它没有考虑到这一点 .

我希望这有帮助！
回复于 2024-04-19T12:26:08+08:00
3
我知道你想“改变一个程序，如果它存在，只删除它，如果它不存在”但我相信只是总是丢弃程序然后重新创建它更简单 . 以下是仅在已存在的情况下删除该过程的方法：
```
IF OBJECT_ID('MyProcedure', 'P') IS NOT NULL
    DROP PROCEDURE MyProcedure
GO
```
第二个参数告诉OBJECT_ID只查找 object_type = 'P' ，which are stored procedures:的对象

AF =聚合函数（CLR）C = CHECK约束D = DEFAULT（约束或独立）F = FOREIGN KEY约束FN = SQL标量函数FS =汇编（CLR）标量函数FT =汇编（CLR）表值函数IF = SQL内联表值函数IT =内部表P = SQL存储过程PC =汇编（CLR）存储过程PG =计划指南PK = PRIMARY KEY约束R =规则（旧式，独立）RF =复制过滤器过程S =系统基表SN =同义词SO =序列对象TF = SQL表值函数

您可以通过以下方式获得完整的选项列表：
```
SELECT name 
FROM master..spt_values
WHERE type = 'O9T'
```
回复于 2024-04-19T12:26:08+08:00
7

从SQL SERVER 2016开始，您可以使用新的 DROP PROCEDURE IF EXISTS .
DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]

参考：https://msdn.microsoft.com/en-us/library/ms174969.aspx

回复于 2024-04-19T12:26:08+08:00
5
我知道这是一篇非常古老的帖子，但由于这出现在热门搜索结果中，因此为使用 SQL Server 2016 SP1 的人添加了最新更新 -
```
create or alter procedure procTest
as
begin
 print (1)
end;
go
```
如果尚不存在，则创建存储过程，但如果存在则更改它 .

Reference
回复于 2024-04-19T12:26:08+08:00

402

我有同样的错误 . 我知道这个线程已经死了但我想在“匿名程序”之外设置另一个选项 .

我这样解决了：

检查存储过程是否存在：

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

但 "CREATE/ALTER PROCEDURE' must be the first statement in a query batch" 仍在那里 . 我这样解决了：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

我最终得到了这段代码：

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 ...

回复于 2024-04-19T12:26:08+08:00

21
这是一种方法，并以这种方式使用它背后的一些推理 . 编辑存储过程并不是很好，但有利有弊......

更新：您还可以在TRANSACTION中包装整个调用 . 在单个事务中包含许多存储过程，可以全部提交或全部回滚 . 包装在事务中的另一个优点是存储过程始终存在于其他SQL连接，只要它们不使用READ UNCOMMITTED事务隔离级别即可！

1）避免改变过程决策 . 我们的流程总是如果EXISTS DROP那么创建 . 如果你采用相同的模式假设新的PROC是所需的过程，那么为改变它而烦恼是因为你有一个IF EXISTS ALTER ELSE CREATE .

2）您必须将CREATE / ALTER作为批处理中的第一个调用，因此您无法在动态SQL之外的事务中包装一系列过程更新 . 基本上，如果您想要运行整个过程更新堆栈或将它们全部回滚而不恢复数据库备份，这是一种在一个批处理中执行所有操作的方法 .
```
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
```
回复于 2024-04-19T12:26:08+08:00
1

我显然没有投票或评论所需的声誉，但我只是想说Geoff使用EXEC（sp_executesql可能更好）的答案绝对是可行的方法 . 删除然后重新创建存储过程最终完成了工作，但是存在一个时刻存储过程根本不存在，这可能非常糟糕，特别是如果这是重复运行的事情 . 我的应用程序遇到了各种各样的问题，因为后台线程正在执行IF EXISTS DROP ... CREATE，同时另一个线程正在尝试使用存储过程 .

回复于 2024-04-19T12:26:08+08:00

在T-Sql中删除和重新创建存储过程的最简单方法是

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

回复于 2024-04-19T12:26:08+08:00

在Sql server 2008以后，您可以使用“ INFORMATION_SCHEMA.ROUTINES ”

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

回复于 2024-04-19T12:26:08+08:00

-2

检查存储过程是否存在

IF EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID
             (N'[Schema].[Procedure_Name]') AND type IN (N'P', N'PC'))
BEGIN
       DROP PROCEDURE [Schema].[Procedure_Name]
       Print('Proceudre dropped => [Schema].[Procedure_Name]')
END

检查IF是否存在触发，功能也可点击以下链接http://www.gurujipoint.com/2017/05/check-if-exist-for-trigger-function-and.html

回复于 2024-04-19T12:26:08+08:00

这是我使用的脚本 . 有了它，我避免不必要地删除和重新创建存储过程 .

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

回复于 2024-04-19T12:26:08+08:00

144

你为什么不这么简单呢？

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

    CREATE Procedure uspBlackListGetAll

..........

回复于 2024-04-19T12:26:08+08:00

2
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]
```
回复于 2024-04-19T12:26:08+08:00
3

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

见MyDbUtils @ CodePlex .

回复于 2024-04-19T12:26:08+08:00

109

我想知道！为什么我不写像整个查询

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本身正在处理已存在的事情这是我一直对我所有人做的事情客户！

回复于 2024-04-19T12:26:08+08:00

3

创建程序如果不存在'你的proc-name'（）开始......结束

回复于 2024-04-19T12:26:08+08:00

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

18 回答

相关问题