如何在创建之前检查存储过程是否存在 [英] How to check if a stored procedure exists before creating it

问题描述

我有一个 SQL 脚本，每次客户端执行数据库管理"功能时都必须运行该脚本.该脚本包括在客户端数据库上创建存储过程.这些客户端中的一些可能在运行脚本时已经拥有存储过程，而另一些可能没有.我需要将缺少的存储过程添加到客户端数据库中，但是无论我如何尝试弯曲 T-SQL 语法，我都得到了

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

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?

推荐答案

您可以在任何能够运行查询的地方运行过程代码.

You can run procedural code anywhere you are able to run a query.

只需复制AS之后的所有内容:

Just copy everything after 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.

这很像PL/SQL中所谓的匿名过程.

That's much like what is called anonymous procedure in PL/SQL.

更新:

你的问题标题有点混乱.

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.

以下是 SSMS 在创建脚本中的输出:

Here's what SSMS outputs in the create script:

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'myproc')
                    AND type IN ( N'P', N'PC' ) ) 
DROP …
CREATE …

更新:

包含架构时如何执行此操作的示例:

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

在上面的例子中，dbo 是架构.

In the example above, dbo is the schema.

更新:

在 SQL Server 2016+ 中，你可以这样做

In SQL Server 2016+, you can just do

创建或更改程序 dbo.MyProc

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