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,

  1. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HelloWorldSP]'))  
  2.     EXEC sp_executesql   
  3.         N'CREATE PROCEDURE [dbo].[HelloWorldSP]  
  4.         AS  
  5.         BEGIN  
  6.             SET NOCOUNT ON;  
  7.             PRINT ''I love SQL Server. SP CREATED''  
  8.         END'  
  9. ELSE  
  10.     EXEC sp_executesql   
  11.         N'ALTER PROCEDURE [dbo].[HelloWorldSP]  
  12.         AS  
  13.         BEGIN  
  14.             SET NOCOUNT ON;  
  15.             PRINT ''I love SQL Server. SP ALTERED''  
  16.         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.
  1. CREATE OR ALTER PROCEDURE [dbo].[HelloWorldSP]  
  2. AS  
  3. BEGIN  
  4.     SET NOCOUNT ON;  
  5.     PRINT 'I love SQL Server.'  
  6. 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.
Ebook Download
View all
Learn
View all