DROP/CREATE vs ALTER on SQL Server Stored Procedures

We have a number of migration scripts that modify the database as our applications progress. As a result we have occasion to alter stored procedures, but during development the migrations may be run out of sequence on various test and development databases. We sometimes don’t know if a stored procedure will exist on a particular database because of the state of various branches of development.

So, which is better, dropping a stored procedure and recreating it, or just simply altering the existing one, and if we’re altering it, what needs to happen if it simply doesn’t exist already?

DROP / CREATE

This is the code we used for the DROP/CREATE cycle of adding/updating stored procedures

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
BEGIN
       DROP PROCEDURE MyProc;
END
GO
 
CREATE PROCEDURE MyProc
AS
	-- Body of proc here
GO

CREATE / ALTER

We recently changed the above for a new way of dealing with changes to stored procedures. What we do now is detect if the procedure exists or not, if it doesn’t then we create a dummy. Regardless of whether the stored procedure previously existed or not, we can now ALTER the stored procedure (whether it was an existing procedure or the dummy one we just created

That code looks like this

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
BEGIN
       EXEC('CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END')
END
GO
 
ALTER PROCEDURE [dbo].[MyProc]
AS
	-- Body of proc here
GO

This looks counter-intuitive to create a dummy procedure just to immediately alter it but it has some advantages over drop/create.

If an error occurs with drop/create while creating the stored procedure and there had been a stored procedure to drop originally you are now left without anything. With the create/alter in the event of an error when altering the stored procedure the original is still available.

Altering a stored procedure keeps any security settings you may have on the procedure. If you drop it, you’ll lose those settings. The same goes for dependencies.