When working with SQL Server databases, it is common to encounter situations where you need to check if an object already exists before creating or dropping it. This is where the T-SQL IF EXISTS statement comes in handy. In this article, we will explore the different versions of the IF EXISTS statement and how to use it effectively.

IF EXISTS in SQL 2014 or Before

In SQL Server 2014 or earlier versions, if you try to create an object that already exists in the database, you will receive an error message. To avoid this, developers often use the IF EXISTS statement to check if the object exists before creating it.

For example, let’s say we want to deploy a stored procedure called ‘stpGetAllMembers’ in the SQLShack test database. We can use the following code to check if the procedure already exists:

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

If the procedure exists, the code inside the BEGIN statement will be executed and the message ‘Stored procedure already exists’ will be printed. If the procedure does not exist, the code inside the BEGIN statement will not be executed.

Similarly, we can use the IF EXISTS statement to check for the existence of other SQL Server objects such as tables, views, constraints, and more.

IF EXISTS in SQL Server 2016 or Later

In SQL Server 2016 and later versions, a new enhancement was introduced called the DROP IF EXISTS command. This command allows you to drop an object if it already exists without generating an error.

For example, to drop a stored procedure called ‘stpGetAllMembers’, you can use the following code:

DROP PROCEDURE IF EXISTS stpGetAllMembers;

If the procedure exists, it will be dropped. If it does not exist, no error will be generated.

The DROP IF EXISTS command can be used with other SQL Server objects such as views, constraints, tables, and even databases. It provides a more concise and efficient way to handle object existence checks.

Conclusion

In this article, we have explored the different versions of the T-SQL IF EXISTS statement in SQL Server. Whether you are working with SQL Server 2014 or earlier versions, or SQL Server 2016 and later versions, there is a suitable syntax available for you to check the existence of objects before creating or dropping them. It is recommended to use the latest syntax (DROP [object] IF EXISTS) if you are on SQL Server 2016 or later versions for better efficiency and readability.