Purpose
The purpose of this tutorial is to show some examples of using IF EXISTS with Tables and Stored Procedures common to enterprise SQL DDL scripts. Usually when only one or two individuals are involved with scripting and deploying SQL scripts to a database, typically there is no need to use additional "fail-safes" for tables or to ensure a stored procedure is deployed without errors. However in a large enterprise, particularly when you do not have sufficient permissions to deploy and execute SQL scripts and need to hand-off the scripts to others who have no detailed understanding of what is being deployed and likely will not peer review the content, it becomes critical to use "fail-safes" to minimize potential problems. For example, if you hand-off a SQL script to another team and more than one individual on that team runs your script (let's say, to create a table) it may run fine for one individual but the second individual will get a database error because the table already existed.
IF EXISTS can help regulate what is done in the database, particularly if the script is run more than once. In this example for a DDL script, IF EXISTS with a table will be shown as well as a stored procedure.
The Table
USE [some-database]
GO
-- A new table
IF NOT EXISTS (
SELECT * FROM sys.tables t JOIN sys.schemas s ON (t.schema_id = s.schema_id)
WHERE s.[name] = 'dbo' AND t.[name] = 'my-table')
BEGIN
CREATE TABLE [dbo].[my-table]
(
id INT PRIMARY KEY IDENTITY (1,1),
notes VARCHAR(100)
);
END
GO
The Stored Procedure
-- A new SP with drop
IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = object_id('dbo.my-stored-procedure'))
DROP PROCEDURE [dbo].[my-stored-procedure];
GO
CREATE PROCEDURE [dbo].[my-stored-procedure]
(
@id INT,
@notes VARCHAR(100) = NULL
)
AS
BEGIN
SET NOCOUNT ON;
-- Do something...
END
GO
