When I make changes to a stored procedure, function or trigger I usually create a script and put it into a folder that is part of a source control system.
In the script I use the following Sql to drop the stored procedure/function/trigger. This means that the script will always work and will always result in the stored procedure(etc.) being created. (Yes I know that the script still might fail if there are errors in it.)
When creating scripts I usually start with the following code which drops the stored procedure (obviously the drop is altered for triggers and functions) :
IF EXISTS (
SELECT 1 FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[MySproc]'))
DROP PROCEDURE [dbo].[MySproc];
PRINT '[dbo].[MySproc] - Dropped';
I then put the stored procedure/function/trigger definition and another “GO” obviously!
Then at the end of the procedure definition, after the GO, I put the following:
PRINT '[dbo].[MySproc] - Created';
Note that the PRINT’s are not needed, but I try and make it a habit to put them in anyway.