Template de un SP con captura de excepciones

sp_template.sql

IF OBJECT_ID ('spa_XXX_ACT_NOMBRE') IS NOT NULL
	DROP PROCEDURE spa_XXX_ACT_NOMBRE
GO

CREATE PROCEDURE spa_XXX_ACT_NOMBRE
AS
/*
!IniHeaderDoc
*****************************************************************************
!NombreObjeto     : spa_XXX_ACT_NOMBRE
!Sistema          : Proyecto
!Modulo           : Web
!Descripcion      : Descripción
!Plataforma       : !BaseDatosSqlServer
!Perfil           : USOGENERAL
!Itinerado        : N.A
!Uso              : N.A
!Autor            : Cesar Gonzalez
!Creacion         : DD/MM/YYYY
!Retornos/Salidas : N.A
!OrigenReq        : N.A
=============================================================================
!Parametros :
=============================================================================
!Testing    : exec spa_XXX_ACT_NOMBRE
=============================================================================
!ControlCambio
--------------
!cVersion    !cFecha        !cProgramador                !cDescripcion
-----------------------------------------------------------------------------
1.0           DD/MM/YYYY    Cesar Gonzalez               Creación del objeto
*****************************************************************************
!EndHeaderDoc
*/


BEGIN TRANSACTION transaccion_sp
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
    
        -- QUERIES


        COMMIT TRANSACTION transaccion_sp
        RETURN 0
    END TRY


    BEGIN CATCH
    
        -- En caso de error capturo la información
        -- asociada para almacenar y/o desplegar
                 
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;
        DECLARE @ErrorNumber INT;      
    
        SELECT   
            @ErrorMessage    = ERROR_MESSAGE(),  
            @ErrorSeverity   = ERROR_SEVERITY(),  
            @ErrorState      = ERROR_STATE(),
            @ErrorNumber     = ERROR_NUMBER();
                     
        ROLLBACK TRANSACTION transaccion_sp
        
        RAISERROR (@ErrorMessage,
                   @ErrorSeverity,
                   @ErrorState);  
                   
        RETURN (@ErrorNumber)
    END CATCH
END

Se agrega una cabecera para validar la existencia del SP en la base de datos, de manera de evitar conflictos cuando es una actualización de un procedimiento almacenado ya existente