Rajendra Gupta
Code Snippet for SQL database in Azure Data Studio

Overview of the T-SQL If Exists statement in a SQL Server database

March 3, 2020 by

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

  • IF EXISTS in SQL 2014 or before
  • DROP ..IF EXISTS in SQL Server 2016 to 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.

object already exists error message

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

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

Cannot drop the procedure 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.

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.

The following code does the below things for us:

  • First, it executes the select statement inside the IF Exists
  • If the select statement returns a value that condition is TRUE for IF Exists
  • It starts the code inside a begin statement and prints the message

object already exists

If the procedure does not exist, it does not run the code in a begin statement.

T-SQL If Exists syntax

Now, we want to drop this procedure if it already exists in the database. We can add a DROP PROCEDURE command inside a begin statement. It drops the stored procedure if it already exists in the database.

We can use this method with other SQL Server objects as well. The following query checks for the SQL table existence and drops it if it is there.

Drop a procedure

In the article, SQL Code Snippets in Azure Data Studio, we discussed code snippets for writing T-SQL. I thought of checking the syntax for the sqlCreateStoredProc snippet for a new stored procedure.

To view this snippet definition, type create proc, press the sqlCreateStoredProc and enter.

Snippet definition

As shown in the following image, this snippet also uses the IF EXISTS method for writing a stored procedure.

Code Snippet for SQL database  in Azure Data Studio

DROP IF EXISTS statement

SQL Server 2016 provides an enhancement to check the object’s existence and drop if it already exists. It introduces DROP IF EXISTS command for this purpose.

The syntax for DROP IF EXISTS

DROP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME

  • It drops the object if it already exists in the SQL database
  • We can also use it to drop the column or constraints as well
  • If the specified object does not exist, it does not give any error message. It continues the execution for the next command

We can use Database, Table, Function, Trigger, Stored Procedure, Column, User, View, Schema, Index, Role for this command.

Let’s understand this new T-SQL IF EXISTS clause using various examples.

DROP Stored Procedure IF EXISTS

In the previous example, we used DROP Stored Procedure stpGetAllMembers for demonstration purposes. This stored procedure does not exist now. Let’s use the new method and try to drop the stored procedure.

DROP PROCEDURE IF EXISTS for SQL database

We can add further code such as print statements, create statements. The following code prints a message after the DROP PROCEDURE command.

Let’s create the stored procedure using this new syntax T-SQL IF EXISTS command. The following query drops the stored procedure if it already exists, in case it does not exist, it will create one.

DROP View IF EXISTS

We can use the following query for SQL 2016 or later to drop a view.

In SQL Server 2014 or earlier version, we use the following code. It checks the views using sys.objects for type V.

DROP Constraint IF EXISTS

We can use the following command to drop a UNIQUE constraint using DROP CONSTRAINT IF EXISTS statement.

The following code works in SQL 2014 or before. Type UQ in the sys.objects refers to a UNIQUE constraint.

For a check constraint, change the type from UQ to C in the SQL 2014 or before version query. We do not need to change the query specified using DROP CONSTRAINT IF EXISTS statement.

DROP Table IF EXISTS

We can use DROP TABLE IF EXISTS statement to drop a SQL table as well in SQL Server 2016 or later.

For SQL 2014 or previous versions, we need to use IF EXISTS method as shown below. Type U refers to a user-defined SQL table.

DROP Database IF EXISTS

We can use the new T-SQL If Exists scripts for dropping a SQL database as well for SQL 2016 or later.

Alternatively, use the following script with SQL 2014 or lower version. It is also valid in the higher SQL Server versions as well.

DROP Column IF EXISTS

Sometimes we require to drop a column from a SQL table. It is a good practice as well to drop unwanted columns as well. It saves efforts for the SQL engine and improves query performance while retrieving fewer records for the output.

We drop a column using the Alter Table statement. We can leverage DROP COLUMN IF EXISTS as well in this alter statement. Suppose we want to drop a Country column from the Employee table. We use the following alter table statement.

It is a single line and a straightforward query. Alternately, we use the following query using IF Exists. It seems to be more complicated than the earlier one. We should use the latest query syntax with the supported SQL Server versions.

Conclusion

In this article, we explored two different versions of T-SQL IF Exists statements.

  • IF EXISTS in SQL 2014 or before
  • DROP [Object] IF EXISTS in SQL Server 2016 or later

You should choose the syntax suitable for you. I would recommend the latest one (Drop [object[ IF Exists), if you are on SQL 2016 or later versions.

Rajendra Gupta
1,719 Views