Create Or Alter In SQL Server 2016

Introduction

There are many new features and language enhancements that have been made with the introduction of SQL Server 2016. This is a very small tip but can come in as handy for some cases. Let’s think about a situation where we want to modify an object (Ex: Stored Procedure) and we are not sure if it already exists in the target instance. For earlier versions, what we usually do is to check for the existence of the object with the help of IF…ELSE… conditional statement and decide to execute either the CREATE or ALTER statement in the respective block. Well, with the new syntax, you’ll be able to avoid the additional headache and save time writing all these logics.

Demonstration

Currently this feature is available only for the following object types,

• Stored Procedure
• Function
• Trigger
• View

Let’s quickly look at how we used to handle this situation in earlier versions,

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HelloWorldSP]'))
    EXEC sp_executesql
        N'CREATE PROCEDURE [dbo].[HelloWorldSP]
        AS
        BEGIN
            SET NOCOUNT ON;
            PRINT ''I love SQL Server. SP CREATED''
        END'
ELSE
    EXEC sp_executesql
        N'ALTER PROCEDURE [dbo].[HelloWorldSP]
        AS
        BEGIN
            SET NOCOUNT ON;
            PRINT ''I love SQL Server. SP ALTERED''
        END'

As you can see, the CREATE or ALTER query has to be executed with the help of dynamic sql and sp_executesql. Now think about a huge stored procedure, it will be a very tedious job to format the content.

 

Now, let's see the new syntax for the same.

CREATE OR ALTER PROCEDURE [dbo].[HelloWorldSP]
AS
BEGIN
    SET NOCOUNT ON;
    PRINT 'I love SQL Server.'
END

Isn't it very simple and useful?

Conclusion

Hopefully, you liked this quick tip. Please share your views on this. Waiting for your feedbacks!

 

Thanks a lot for reading this.