Cacher is the code snippet organizer for pro developers

IF EXISTS(SELECT * FROM sysobjects WHERE type = 'P' and name = 'ProsedureName')
BEGIN
DROP PROCEDURE ProsedureName
END
GO

-- EXEC ProsedureName
-- =============================================
-- Author :	
-- Create date: 
-- Reference :	
-- Description:	
-- =============================================

CREATE PROCEDURE [dbo].ProsedureName
(
	@pCustomerId int
)
-- WITH ENCRYPTION
AS

BEGIN TRY
	SET NOCOUNT ON;
	SET DATEFORMAT dmy;

	/* Reassign the input parameters to local parameters */
	DECLARE @CustomerId int = @pCustomerId

	/* Transaction if required */
	BEGIN TRAN;
	COMMIT TRAN;
END TRY

BEGIN CATCH
	IF @@TRANCOUNT > 0
	ROLLBACK TRANSACTION;

	/* If error occured the will show this message */
	SELECT 'ErrorNumber :' + CAST(ERROR_NUMBER() AS varchar(max)) 
	+ ', ErrorState : ' + CAST( ERROR_STATE() AS varchar(max))
	+ ', ErrorProcedure : ' + CAST( ERROR_PROCEDURE() AS varchar(max))
	+ ', ErrorLine : ' + CAST( ERROR_LINE() AS varchar(max))
	+ ', ErrorMessage : ' + CAST( ERROR_MESSAGE() AS varchar(max));
END CATCH