SQL 2016: Create or Alter
Author: jason
Date: 2019-01-17 11:04:14
Category: Technical

Another cool addition to SQL 2016 is CREATE OR ALTER, as in:

CREATE OR ALTER dbo.sp_someProcedure

You don't have to know whether the stored procedure is there or not. SQL takes care of that for you. It works for Views, Functions, Triggers, and Stored Procedures.

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/

https://www.mssqltips.com/sqlservertip/4640/new-create-or-alter-statement-in-sql-server-2016-sp1/

Code


/*
Previously when you wanted to modify a
Stored Procedure or View,
you had to ALTER or DROP/CREATE it.

Now, you can edit the content,
and execute it multiple times with no fails
and no need to remember if it already exists
*/

CREATE OR ALTER PROCEDURE [dbo].[some_sproc]
AS
BEGIN
SET NOCOUNT ON;
PRINT 'So Cool!';
END
GO

CREATE OR ALTER VIEW [dbo].[some_view]
AS SELECT '1' AS One;
GO







jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009