IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'sp_SQLAuthority' AND TYPE = 'P') BEGIN DROP PROCEDURE sp_SQLAuthority END GO CREATE PROCEDURE sp_SQLAuthority (@Params ...) AS BEGIN ... END GO
So, if you are deploying any script to SQL Server and you are not aware of the situation in production, whether a stored proc was already previously deployed or not. Developer need to put a check and drop if it’s already there and then create it.
Oracle has been having CREATE or REPLACE from a long time and there have been feedbacks given to the Microsoft SQL product team to do something about it.
CREATE OR ALTER statement
Create or Replace xxx
Finally, they have heard it and introduced this change in Service Pack 1 of SQL Server 2016. Here are the few objects which would allow such syntax
- Stored procedures
- User-defined functions
Unfortunately, it would not work for Objects that require storage changes like table, index. It is not allowed for CLR procedure also.
Here is the usage
CREATE OR ALTER PROCEDURE usp_SQLAuthority AS BEGIN SELECT 'ONE' END GO
Do you think this is a good enhancement? I would highly encourage to read below blog on a similar feature. SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause
Were you aware of this? Do you want me to blog about SP1 enhancement?
Reference: Pinal Dave (https://blog.sqlauthority.com)
That is fantastic, especially in development when you have procedures in source control and are constantly revising them.
Yeah. this was a long ask from Microsoft because Oracle has it from long time.
Let assume if two developer used same name for the stored procedure means, in this case one person stored procedure will get over written by second one right. How it will be ok?
It is never okey. This feature is not supposed to use that way.
Hey Pinal…..Perfect for Dev and other lower environments but I would be uncomfortable leveraging it in Prod environment apart from when it is used to hold temporary data or temporary code….Thank you.
@Anil – i didn’t get you point.
At my last shop when lot many deployment changes were getting canceled/rolled back, DBA team and client SME were asked to streamline the process for deploying the changes in production environment and we decided as follows.
1.Script Validation: No excuses but get latest copy of production database and restore same on Dev server – deploy the changes, rollback the changes and then re-deploy the changes. All goes fine, script validation is successful.
2.Code review: Walk through the code and let DBA ask questions/clarifications about the same. DROP, TRUNCATE and ALTER query would certainly be discussed and if a SP is going to be overwritten by new code, it would be renamed and maintained till next release. As pointed out by you, before overriding some piece of code, one should see what is being overridden and without leveraging CREATE OR ALTER, one has to review the existing code.
3.Deploy only validated script and any new changes without testing should be deployed with application owner’s approval in unusual circumstances.
While this process may not be full proof but could be helpful to avoid unwanted issues. It is great that Microsoft is listening to its customers and adding value to amazing SQL Server.
Hope I made myself clear this time !
@Anil – You can still use the same process :)