Template for SQL Server Stored Procedures

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

Leave a Reply

Your email address will not be published. Required fields are marked *