如果存储过程不存在，则创建它

本文介绍了如果存储过程不存在，则创建它的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想检查存储过程列表是否存在.我希望这一切都在 1 个脚本中一个一个地完成.到目前为止，我有这种格式:

I want to check if a list of stored procedures exist. I want this all to be done in 1 script, one by one. So far I have this format:

USE [myDatabase] GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_1') BEGIN CREATE PROCEDURE sp_1 AS ................. END GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_2') BEGIN CREATE PROCEDURE sp_2 AS ................. END GO

等等.但是，我收到以下错误:

and so on. However, I'm getting the following error:

关键字Procedure"附近的语法不正确.

Incorrect syntax near the keyword 'Procedure'.

为什么我所做的工作不正常?

Why isn't what I'm doing working correctly?

推荐答案

CREATE PROCEDURE 必须是批处理中的第一条语句.我通常这样做:

CREATE PROCEDURE must be the first statement in the batch. I usually do something like this:

IF EXISTS ( SELECT type_desc, type FROM sys.procedures WITH(NOLOCK) WHERE NAME = 'myProc' AND type = 'P' ) DROP PROCEDURE dbo.myProc GO CREATE PROC dbo.myProc AS .... GO GRANT EXECUTE ON dbo.myProc TO MyUser

(不要忘记 grant 语句，因为如果您重新创建 proc，它们将会丢失)

(don't forget grant statements since they'll be lost if you recreate your proc)

在部署存储过程时要考虑的另一件事是删除可以成功而创建失败.我总是在出现问题时回滚我的 SQL 脚本.只要确保你最后没有不小心删除提交/回滚代码，否则你的 DBA 可能会把你踢到气管里:)

One other thing to consider when you are deploying stored procedures is that a drop can succeed and a create fail. I always write my SQL scripts with a rollback in the event of a problem. Just make sure you don't accidentally delete the commit/rollback code at the end, otherwise your DBA might crane-kick you in the trachea :)

BEGIN TRAN IF EXISTS ( SELECT type_desc, type FROM sys.procedures WITH(NOLOCK) WHERE NAME = 'myProc' AND type = 'P' ) DROP PROCEDURE myProc GO CREATE PROCEDURE myProc AS --proc logic here GO -- BEGIN DO NOT REMOVE THIS CODE (it commits or rolls back the stored procedure drop) IF EXISTS( SELECT 1 FROM sys.procedures WITH(NOLOCK) WHERE NAME = 'myProc' AND type = 'P' ) COMMIT TRAN ELSE ROLLBACK TRAN -- END DO NOT REMOVE THIS CODE