Creating or Altering an Objects with a Single Statement

Do you get tired of having your CREATE PROCEDURE statement failing if the stored procedure already exists? If you are like me then you probably hate this as much as I do. I get tired of writing a drop then create statement like below:

IF OBJECT_ID('dbo.YourObject','U') IS NOT NULL
DROP PROCEDURE dbo.YourObject;
CREATE PROCEDURE dbo.YourObject AS _

Or modifying my CREATE PROCEDURE statement to be an ALTER PROCEDURE after my initial creation.  Well now there is a better way.

With the introduction of SQL Server 2016 SP1 Microsoft introduced the CREATE OR ALTER statement. Yes, you read that right the “CREATE OR ALTER” statement. This is not two statements; it is just a single statement. If the object doesn’t exist it will create it. If the object does exist it will alter it. This statement works on stored procedures, views, functions, and triggers. If you are running SQL Server 2016 SP1 or above, you can now create or alter a stored procedure using a statement similar to below.

CREATE OR ALTER PROCEDURE dbo.YourObject AS _

# # #

» See All Articles by Columnist Gregory A. Larsen