CREATE OR ALTER statement in SQL Server 2016 SP1

Microsoft is taking steps to minimize the amount of code that has to be written to get a job done. With that said, they finally introduced with SP1 for SQL Server 2016 the CREATE OR ALTER statement. Previously, if you wanted to script out an object such as a stored procedure you had to check if it exists. This new statement eliminates that.

Step 1 - Example of the OLD WAY

USE [MASTER]
GO
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'MYPROC' 
		AND TYPE = 'P')
DROP PROCEDURE DBO.MYPROC
GO
CREATE PROCEDURE DBO.MYPROC
AS
    BEGIN
        PRINT 'HELLO FROM THOMAS LIDDLE'
    END
GO
EXEC DBO.MYPROC
					


					

					


Step 2 - Example of the New Way

In this step, we are going to insert some sample data.  The sample data is the current employees elections for the year 2017.  Employee number 2, only has benefits for himself in 2017.





					
USE [MASTER]
GO
-- CHANGED THE CREATE STATEMENT IN STEP 1
-- TO CREATE OR ALTER IN THIS STEP.

CREATE OR ALTER PROCEDURE DBO.MYPROC
AS
BEGIN
    PRINT 'HELLO FROM WWW.THOMASLIDDLEDBA.COM'
END
GO
EXEC DBO.MYPROC