SQL Server 2016 Service Pack 1 introduced CREATE OR ALTER. I use it for repeatable deployments of supported object types.
The original workaround checked for the procedure, dropped it, and created it again. CREATE OR ALTER creates a missing object or alters an existing one, avoiding that routine drop-and-recreate pattern.
-- Run in a disposable development database. GO is an SSMS/sqlcmd batch separator.
CREATE OR ALTER PROCEDURE dbo.usp_SQLAuthority
AS
BEGIN
SET NOCOUNT ON;
SELECT 'ONE' AS Result;
END;
GO
EXEC dbo.usp_SQLAuthority;
GOSupported statements cover procedures, views, triggers and user-defined functions. CREATE OR ALTER TABLE and CREATE OR ALTER INDEX remain invalid. I initially excluded CLR procedures. Current CREATE PROCEDURE documentation includes OR ALTER in CLR syntax; check the exact target engine.
Use a schema-qualified name and a deployment account with the required permissions. GO belongs to clients such as SSMS and sqlcmd, not to T-SQL sent as a single command by an application. Repeated execution should leave the intended procedure definition in place, while dependencies and behavior still need application tests.
Reference: CREATE PROCEDURE syntax and version support.
Related reading
CREATE OR ALTER is not valid for every object, it is a convenience within the statements that support it.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
8 Comments. Leave new
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.
Hi Pinal,
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.
Hi Pinal,
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 :)