如何检测存储过程是否已经存在 [英] How to detect if a stored procedure already exists

问题描述

我必须编写一个部署脚本，它可以在存储过程存在或不存在时工作.即如果它存在，那么我需要改变它，否则创建它.

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.

如何在 sql 中执行此操作.

How can I do this in the sql.

我使用的是 SQL Server 2005

I am using SQL Server 2005

推荐答案

如果您 DROP 和 CREATE 过程，您将失去安全设置.这可能会惹恼您的 DBA 或完全破坏您的应用程序.

If you DROP and CREATE the procedure, you will loose the security settings. This might annoy your DBA or break your application altogether.

如果尚不存在，我要做的是创建一个简单的存储过程.之后，您可以根据自己的喜好更改存储过程.

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.

这篇关于如何检测存储过程是否已经存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！