SQL 如何在创建之前检查存储过程是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2072086/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if a stored procedure exists before creating it
提问by The Shaper
I have a SQL script that has to be run every time a client executes the "database management" functionality. The script includes creating stored procedures on the client database. Some of these clients might already have the stored procedure upon running the script, and some may not. I need to have the missing stored procedures added to the client database, but it doesn't matter how much I try to bend T-SQL syntax, I get
我有一个 SQL 脚本，每次客户端执行“数据库管理”功能时都必须运行该脚本。该脚本包括在客户端数据库上创建存储过程。这些客户端中的一些可能在运行脚本时已经拥有存储过程，而另一些可能没有。我需要将缺少的存储过程添加到客户端数据库中，但是无论我如何尝试弯曲 T-SQL 语法，我都得到了
CREATE/ALTER PROCEDURE' must be the first statement in a query batch
CREATE/ALTER PROCEDURE' 必须是查询批处理中的第一条语句
I've read that dropping before creating works, but I don't like doing it that way.
在创作作品之前，我已经阅读过那个下降，但我不喜欢那样做。
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
DROP PROCEDURE MyProc
GO
CREATE PROCEDURE MyProc
...
How can I add check for the existence of a stored procedure and create it if it doesn't exist but alter it if it does exist?
如何添加对存储过程是否存在的检查，如果它不存在则创建它，但如果它存在则更改它？
采纳答案by Quassnoi
You can run procedural code anywhere you are able to run a query.
您可以在任何能够运行查询的地方运行过程代码。
Just copy everything after AS:
只需复制以下内容AS：
BEGIN
DECLARE @myvar INT
SELECT *
FROM mytable
WHERE @myvar ...
END
This code does exactly same things a stored proc would do, but is not stored on the database side.
此代码与存储过程执行的操作完全相同，但未存储在数据库端。
That's much like what is called anonymous procedure in PL/SQL.
这很像PL/SQL.
Update:
更新：
Your question title is a little bit confusing.
你的问题标题有点混乱。
If you only need to create a procedure if it not exists, then your code is just fine.
如果您只需要在它不存在的情况下创建一个过程，那么您的代码就可以了。
Here's what SSMSoutputs in the create script:
以下是SSMS创建脚本中的输出：
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'myproc')
AND type IN ( N'P', N'PC' ) )
DROP …
CREATE …
Update:
更新：
Example of how to do it when including the schema:
包含架构时如何执行此操作的示例：
IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = object_id(N'[dbo].[MyProc]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE [dbo].[MyProc]
END
In the example above, dbois the schema.
在上面的示例中，dbo是架构。
Update:
更新：
In SQL Server 2016+, you can just do
在 SQL Server 2016+ 中，你可以这样做
CREATE OR ALTER PROCEDURE dbo.MyProc
CREATE OR ALTER PROCEDURE dbo.MyProc
回答by Geoff
I realize this has already been marked as answered, but we used to do it like this:
我意识到这已经被标记为已回答，但我们曾经这样做过：
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
....
Just to avoid dropping the procedure.
只是为了避免放弃程序。
回答by MrChips
If you're looking for the simplest way to check for a database object's existence before removing it, here's one way (example uses a SPROC, just like your example above but could be modified for tables, indexes, etc...):
如果您正在寻找在删除数据库对象之前检查数据库对象是否存在的最简单方法，这是一种方法（示例使用 SPROC，就像上面的示例一样，但可以针对表、索引等进行修改...）：
IF (OBJECT_ID('MyProcedure') IS NOT NULL)
DROP PROCEDURE MyProcedure
GO
This is quick and elegant, but you need to make sure you have unique object names across all object types since it does not take that into account.
这是快速而优雅的，但您需要确保在所有对象类型中都有唯一的对象名称，因为它没有考虑到这一点。
I Hope this helps!
我希望这有帮助！
回答by Michael Currie
I know you want to "alter a procedure if it exists and only delete it if it does not exist" but I believe it's simpler to just always drop the procedure and then re-create it. Here's how to drop the procedure only if it already exists:
我知道你想“如果程序存在就改变它，如果它不存在就删除它”，但我相信总是删除程序然后重新创建它更简单。以下是仅当该过程已存在时才删除该过程的方法：
IF OBJECT_ID('MyProcedure', 'P') IS NOT NULL
DROP PROCEDURE MyProcedure
GO
The second parameter tells OBJECT_IDto only look for objects with object_type = 'P', which are stored procedures:
第二个参数告诉OBJECT_ID只查找带有 的对象object_type = 'P'，它们是存储过程：
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
TF = SQL table-valued-function
TR = Trigger
AF = 聚合函数 (CLR)
C = 检查约束
D = DEFAULT（约束或独立）
F = 外键约束
FN = SQL 标量函数
FS = 汇编 (CLR) 标量函数
FT = 汇编 (CLR) 表值函数
IF = SQL 内联表值函数
IT = 内表
P = SQL 存储过程
PC = 汇编 (CLR) 存储过程
PG = 计划指南
PK = PRIMARY KEY 约束
R = 规则（旧式，独立）
RF = 复制过滤程序
S = 系统基表
SN = 同义词
SO = 序列对象
TF = SQL 表值函数
TR = 触发器
You can get the full list of options via:
您可以通过以下方式获取完整的选项列表：
SELECT name
FROM master..spt_values
WHERE type = 'O9T'
回答by Hybris95
As of SQL SERVER 2016 you can use the new DROP PROCEDURE IF EXISTS.DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]
从 SQL SERVER 2016 开始，您可以使用新的DROP PROCEDURE IF EXISTS.DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]
Reference : https://msdn.microsoft.com/en-us/library/ms174969.aspx
回答by gkb
I know it is a very old post, but since this appears in the top search results hence adding the latest update for those using SQL Server 2016 SP1-
我知道这是一篇很旧的帖子，但由于它出现在顶部搜索结果中，因此为使用SQL Server 2016 SP1 的用户添加了最新更新-
create or alter procedure procTest
as
begin
print (1)
end;
go
This creates a Stored Procedure if does not already exist, but alters it if exists.
如果不存在，则创建存储过程，但如果存在则更改它。
回答by JayJay
DROP IF EXISTS is a new feature of SQL Server 2016
DROP IF EXISTS 是 SQL Server 2016 的新功能
DROP PROCEDURE IF EXISTS dbo.[procname]
回答by Oaxas
I had the same error. I know this thread is pretty much dead already but I want to set another option besides "anonymous procedure".
我有同样的错误。我知道这个线程已经死了，但我想设置除了“匿名程序”之外的另一个选项。
I solved it like this:
我是这样解决的：
Check if the stored procedure exist:
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 ENDHowever 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 ...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 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 ...
回答by Shiv
Here's a method and some reasoning behind using it this way. It isn't as pretty to edit the stored proc but there are pros and cons...
这是一种方法以及以这种方式使用它的一些原因。编辑存储的过程并不那么漂亮，但有利有弊......
UPDATE: You can also wrap this entire call in a TRANSACTION. Including many stored procedures in a single transaction which can all commit or all rollback. Another advantage of wrapping in a transaction is the stored procedure always exists for other SQL connections as long as they do not use the READ UNCOMMITTED transaction isolation level!
更新：您还可以将整个调用包装在 TRANSACTION 中。在单个事务中包含许多存储过程，可以全部提交或全部回滚。包装在事务中的另一个优点是存储过程始终存在于其他 SQL 连接中，只要它们不使用 READ UNCOMMITTED 事务隔离级别即可！
1) To avoid alters just as a process decision. Our processes are to always IF EXISTS DROP THEN CREATE. If you do the same pattern of assuming the new PROC is the desired proc, catering for alters is a bit harder because you would have an IF EXISTS ALTER ELSE CREATE.
1) 避免改变就像一个过程决定。我们的流程是始终 IF EXISTS DROP THEN CREATE。如果您以相同的模式假设新的 PROC 是所需的 proc，那么满足更改会有点困难，因为您将拥有 IF EXISTS ALTER ELSE CREATE。
2) You have to put CREATE/ALTER as the first call in a batch so you can't wrap a sequence of procedure updates in a transaction outside dynamic SQL. Basically if you want to run a whole stack of procedure updates or roll them all back without restoring a DB backup, this is a way to do everything in a single batch.
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
回答by Romil Kumar Jain
In Sql server 2008 onwards, you can use "INFORMATION_SCHEMA.ROUTINES"
在 Sql server 2008 以后，你可以使用“ INFORMATION_SCHEMA.ROUTINES”
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MySP'
AND ROUTINE_TYPE = 'PROCEDURE')
