SQL 如何检测存储过程是否已经存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/937908/
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 detect if a stored procedure already exists
提问by GordyII
I have to write a deployment script which will work if a stored procedure exists or does not exist. i.e. if it exists, then I need to alter it, otherwise create it.
我必须编写一个部署脚本，如果存储过程存在或不存在，该脚本将起作用。即如果它存在，那么我需要改变它，否则创建它。
How can I do this in the sql.
我怎样才能在sql中做到这一点。
I am using SQL Server 2005
我正在使用 SQL Server 2005
回答by Andomar
If you DROP and CREATE the procedure, you will loose the security settings. This might annoy your DBA or break your application altogether.
如果您 DROP 和 CREATE 过程，您将失去安全设置。这可能会惹恼您的 DBA 或完全破坏您的应用程序。
What I do is create a trivial stored procedure if it doesn't exist yet. After that, you can ALTER the stored procedure to your liking.
如果尚不存在，我要做的是创建一个简单的存储过程。之后，您可以根据自己的喜好更改存储过程。
IF object_id('YourSp') IS NULL
EXEC ('create procedure dbo.YourSp as select 1')
GO
ALTER PROCEDURE dbo.YourSp
AS
...
This way, security settings, comments and other meta deta will survive the deployment.
这样，安全设置、评论和其他元数据将在部署后继续存在。
回答by Aaron Alton
The cleanest way is to test for it's existence, drop it if it exists, and then recreate it. You can't embed a "create proc" statement inside an IF statement. This should do nicely:
最简洁的方法是测试它是否存在，如果存在则丢弃它，然后重新创建它。您不能在 IF 语句中嵌入“create proc”语句。这应该做得很好：
IF OBJECT_ID('MySproc', 'P') IS NOT NULL
DROP PROC MySproc
GO
CREATE PROC MySproc
AS
BEGIN
...
END
回答by JasonS
If you are dealing only with stored procedures, the easiest thing to do is to probably drop the proc, then recreate it. You can generate all of the code to do this using the Generate Scripts wizard in SQL Server.
如果您只处理存储过程，最简单的方法可能是删除 proc，然后重新创建它。您可以使用 SQL Server 中的生成脚本向导生成所有代码来执行此操作。
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...
回答by P?????
From SQL Server 2016 CTP3you can use new DIEstatements instead of big IFwrappers
从SQL Server 2016 CTP3你可以使用新的DIE语句而不是大IF包装器
Syntax:
句法：
DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]
删除 { 过程 | 程序 } [ 如果存在 ] { [ schema_name. ] 过程 } [ ,...n ]
Query:
询问：
DROP PROCEDURE IF EXISTS usp_name
More info here
更多信息在这里
回答by Luke Schafer
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
where xxxis the proc name
过程xxx名称在哪里
回答by Remus Rusanu
In addition to what has already been said I also like to add a different approach and advocate the use of differential script deployment strategy. Instead of making a stateful script that always checks the current state and acts based on that state, deploy via a series of stateless scripts that upgrade from well known versions. I have used this strategy and it pays off big time as my deployment scripts are now all 'IF' free.
除了已经说过的内容之外，我还想添加不同的方法并提倡使用差异脚本部署策略。与其制作一个始终检查当前状态并根据该状态进行操作的有状态脚本，不如通过一系列从众所周知的版本升级的无状态脚本进行部署。我已经使用了这个策略并且它获得了很大的回报，因为我的部署脚本现在都是“IF”免费的。
回答by shary.sharath
You can write a query as follows:
您可以按如下方式编写查询：
IF OBJECT_ID('ProcedureName','P') IS NOT NULL
DROP PROC ProcedureName
GO
CREATE PROCEDURE [dbo].[ProcedureName]
...your query here....
To be more specific on the above syntax:
OBJECT_IDis a unique id number for an object within the database, this is used internally by SQL Server. Since we are passing ProcedureNamefollowed by you object type Pwhich tells the SQL Server that you should find the object called ProcedureNamewhich is of type procedure i.e., P
更具体地说明上述语法：
OBJECT_ID是数据库中对象的唯一 ID 号，它由 SQL Server 在内部使用。由于我们传递ProcedureName后跟对象类型P，它告诉 SQL Server 你应该找到名为ProcedureName的对象，它是过程类型，即 P
This query will find the procedure and if it is available it will drop it and create new one.
此查询将找到该过程，如果它可用，它将删除它并创建新的过程。
For detailed information about OBJECT_ID and Object types please visit : SYS.Objects
有关 OBJECT_ID 和对象类型的详细信息，请访问：SYS.Objects
回答by Hemanshu Bhojak
IF OBJECT_ID('SPNAME') IS NULL
-- Does Not Exists
ELSE
-- Exists
回答by Adriaan Davel
I have a stored proc that allows the customer to extend validation, if it exists I do not want to change it, if it doesn't I want to create it, the best way I have found:
我有一个存储过程，允许客户扩展验证，如果它存在我不想更改它，如果它不想创建它，我发现的最好方法：
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
回答by shiva kumar kella
The code below will check whether the stored procedure already exists or not.
下面的代码将检查存储过程是否已经存在。
If it exists it will alter, if it doesn't exist it will create a new stored procedure for you:
如果它存在它会改变，如果它不存在它会为你创建一个新的存储过程：
//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
