Talking with our in house DBA, it was mentioned that DROP/CREATE is a bad model to follow when updating stored procedures. However, this is the model used in the stored procedure template provided by Visual Studio 2005/2010. Here is a modified template that creates an empty stored procedure if it doesn’t exist and performs an ALTER on all subsequent calls.
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Stored_Procedure_Name') BEGIN exec sp_executesql N'CREATE PROCEDURE [dbo].[Stored_Procedure_Name] AS select 1' END GO ALTER Procedure Stored_Procedure_Name ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) AS GO GRANT EXEC ON Stored_Procedure_Name TO PUBLIC GO