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
No comments:
Post a comment