Latest Post

Tuesday, 25 July 2017

DROP IF EXISTS Statement in Sql Server 2016

DROP IF EXISTS Statement in Sql Server 2016

Sql server 2016 provided IF EXISTS in the existing DROP statement to check the existence of the object. If the object exists it drops it and if it doesn’t exist it will continue executing the next statement.

The main purpose of this statement to avoid 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
OBJECT_TYPE can be Table, Procedure, View, Function, Database, Trigger, Assembly, Sequence, Index etc.

Example 1: DROP Stored Procedure IF EXISTS

In previous version of SQL server, if we want to drop the stored procedure if exists we use statement like below.

/********** Previous version of SQL SERVER *******/
IF EXISTS (SELECT 1 FROM sys.procedures
          WHERE Name = 'SP_GET_EMPLOYEE')
BEGIN
    DROP PROCEDURE dbo.SP_GET_EMPLOYEE
END

SQL SERVER 2016 provide DROP IF EXISTS feature to reduce the complexity of the developer and reduce to writing the number of code.

Here is the simple code to drop the stored procedure if exist.

/********** SQL SERVER 2016 *******/
DROP PROCEDURE IF EXISTS dbo.SP_GET_EMPLOYEE

Example 2: DROP TABLE IF EXISTS

In previous version of SQL server, if we want to drop the table if exists we use statement like below.

/********** Previous version of SQL SERVER *******/

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

SQL SERVER 2016 provide DROP IF EXISTS feature to reduce the complexity of the developer and reduce to writing the number of code.

Here is the simple code to drop the table  if exist.


/********** SQL SERVER 2016 *******/
DROP PROCEDURE IF EXISTS dbo.SP_GET_EMPLOYEE

Example 3: DROP Database IF EXISTS

In previous version of SQL server, if we want to drop the Database if exists we use statement like below.

/********** Previous version of SQL SERVER *******/

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

SQL SERVER 2016 provide DROP IF EXISTS feature to reduce the complexity of the developer and reduce to writing the number of code.

Here is the simple code to drop the Database if exist.


/********** SQL SERVER 2016 *******/

DROP PROCEDURE IF EXISTS dbo.SP_GET_EMPLOYEE

Government Jobs