SQL with Manoj

 
SQL Server 2016 release was packed with lot of new features, and I tried to cover most of them, [check here]. This includes some of the major new features like, Polybase, Temporal Tables, JSON support, Stretch DB, Row Level Security, Dynamic data Masking, etc. are very unique to the other Database systems in competition.

But Microsoft’s SQL Server team also keeps on adding few features in every release which were already there in other Database systems, so that developers could use those and make their life easier, like the new IF EXISTS option with DROP & ALTER statements I already discussed in my [previous post].

 
Now, with the recent Service Pack 1, one more feature has been added, which developers (mainly from the Oracle background) were missing from long time, and that is CREATE OR ALTER option while creating programming modules, like:

1. Stored Procedures (SP)

2. Functions (UDFs)

3. Views

4. Triggers

 
–> Now you can create a new Stored Procedure without checking its existence, simply by using the new CREATE OR ALTER option, like below:

CREATE OR ALTER PROCEDURE dbo.spgetEmployeeDetails
	@EmpID INT
AS
BEGIN
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName 
	FROM Person.Person
	WHERE BusinessEntityID = @EmpID
END
GO

… you can execute the above code multiple times and it won’t fail. First time this CREATEs the SP, next time it will ALTER it.

 
–> Previously you need to add an IF EXISTS() condition to check if the SP already exists or not. If exists then drop and then create a new SP, like:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'spgetEmployeeDetails')
	DROP PROCEDURE dbo.spgetEmployeeDetails
GO

CREATE PROCEDURE dbo.spgetEmployeeDetails
	@EmpID INT
AS
BEGIN
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName 
	FROM Person.Person
	WHERE BusinessEntityID = @EmpID
END
GO