SQL Server数据库中的T-SQL如果存在语句概述-CSDN博客

This article walks through different versions of the T-SQL IF EXISTS statement for the SQL database using various examples.

本文使用各种示例介绍了用于SQL数据库的T-SQL IF EXISTS语句的不同版本。

IF EXISTS in SQL 2014 or before
如果SQL 2014或更早版本存在
DROP ..IF EXISTS in SQL Server 2016 to SQL Server 2019
将SQL Server 2016中存在的.IF拖放到SQL Server 2019

介绍 (Introduction)

Suppose you want to deploy objects such as tables, procedures, functions in the SQL Server database. If you execute CREATE statements for these objects, and that object already exists in a database, you get message 2714, level 16, state 3 error message as shown below.

假设您要在SQL Server数据库中部署表，过程，函数等对象。如果对这些对象执行CREATE语句，并且该对象已存在于数据库中，则会收到消息2714，级别16，状态3错误消息，如下所示。

You may write a DROP statement before executing the create statement. It works fine if the object exists in the database.

您可以在执行create语句之前编写DROP语句。如果对象存在于数据库中，它将正常工作。

In case the object does not exist, and you try to drop, you get the following error.

如果对象不存在，并尝试删除该对象，则会出现以下错误。

To avoid this situation, usually, developers add T-SQL If Exists statement and drop the object if it is already available in the database.

为了避免这种情况，通常，开发人员添加T-SQL If Exists语句，并删除该对象（如果数据库中已存在该对象）。

Let’s say we wish to deploy a stored procedure ‘stpGetAllMembers’ in the SQLShack test database. We can use multiple methods to check whether the procedure existence in the SQL database but let’s query sys.objects system table for it.

假设我们希望在SQLShack测试数据库中部署存储过程“ stpGetAllMembers”。我们可以使用多种方法来检查该过程是否存在于SQL数据库中，但是让我们查询sys.objects系统表。

The following code does the below things for us:

以下代码为我们完成了以下操作：

First, it executes the select statement inside the IF Exists
首先，它在IF Exists内部执行select语句
TRUE for IF Exists TRUE
It starts the code inside a begin statement and prints the message
它在begin语句中启动代码并输出消息

IF EXISTS
(
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'dbo.stpGetAllMembers')
)
    BEGIN
        PRINT 'Stored procedure already exists';
END;