Tag Archives: DROP PROCEDURE IF EXISTS

DROP IF EXISTS Statement in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , ,

In Sql Server 2016, IF EXISTS is the new optional clause introduced in the existing DROP statement. Basically, it checks the existence of the object, if the object does exists it drops it and if it doesn’t exists it will continue executing the next statement in the batch. Basically it avoids writing if condition and within if condition writing a statement to check the existence of the object.

Syntax: 

DROP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME

WHERE: OBJECT_TYPE like Table, Procedure, View, Function, Database, Trigger, Assembly, Sequence, Index etc.

IF EXISTS: It is an optional clause and if it is mentioned in the DROP statement then it checks the existence of the object, if it exists it will drop otherwise continues executing the next statement in the block without raising any issues.

Let us understand this new IF EXISTS clause in the DROP statement with an extensive list of examples. To demonstrate this clause let us first create a sample Database with a Table and a Stored Procedure.

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
CREATE TABLE dbo.Customers  (Id INT, Name Nvarchar(50))
GO
CREATE PROCEDURE dbo.WelcomeMessage
AS
	SELECT 'Welcome to Sql Server'
GO

DROP Stored Procedure IF EXISTS

[ALSO READ] How to check if a Stored Procedure exists

In Sql Server 2016 we can write a statement like below to drop a Stored Procedure if exists.

DROP PROCEDURE IF EXISTS dbo.WelcomeMessage

DROP PROCEDURE IF EXISTS

If the stored procedure doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the stored procedure WelcomeMessage which is already dropped.

DROP PROCEDURE IF EXISTS Sql Server 2016

From the result it is clear that it will not raise any error if the stored procedure doesn’t exists, it will continue executing the next statement in the batch.

In previous versions of Sql Server we use statement like below to drop the stored procedure if exists.

IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE Name = 'WelcomeMessage')
BEGIN
    DROP PROCEDURE dbo.WelcomeMessage
END

You can go through the article How to check if a Stored Procedure exists to see various options in the previous versions of Sql Server to check for the existence of a Stored Procedure.

DROP TABLE IF EXISTS

[ALSO READ] How to check if a Table exists

In Sql Server 2016 we can write a statement like below to drop a Table if exists.

DROP TABLE IF EXISTS dbo.Customers

DROP TABLE IF EXISTS

If the table doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the Table dbo.Customers which is already dropped.

DROP TABLE IF EXISTS Sql Server 2016

From the result it is clear that it will not raise any error if the Table doesn’t exists, it will continue executing the next statement in the batch.

In previous versions of Sql Server we use statement like below to drop the Table if exists.

IF EXISTS(SELECT 1 FROM sys.Tables 
          WHERE  Name = N'Customers' AND Type = N'U')
BEGIN
  DROP TABLE dbo.Customers
END

You can go through the article How to check if a Table exists to see various options in the previous versions of Sql Server to check for the existence of a Table.

DROP DataBase IF EXISTS

[ALSO READ] How to check if a Database exists

In Sql Server 2016 we can write a statement like below to drop a DataBase if exists.

USE MASTER
GO
DROP DATABASE IF EXISTS SqlHintsDemoDB

DROP DATABASE IF EXISTS

If the Database doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the Database SqlHintsDemoDB which is already dropped.

DROP DATABASE IF EXISTS Sql 2016

From the result it is clear that, it will not raise any error if the Database doesn’t exists, it will continue executing the next statement in the batch.

In previous versions of Sql Server we use statement like below to drop the Database if exists.

IF DB_ID('SqlHintsDemoDB') IS NOT NULL
BEGIN
    DROP DATABASE SqlHintsDemoDB
END

You can go through the article How to check if a Database exists to see various options in the previous versions of Sql Server to check for the existence of a Database.

[ALSO READ]: