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

Template de ejemplo

Este es un TEMPLATE sencillo que se aplica para los 4 verbos (SELECT / UPDATE / DELETE/ UPDATE) para el manejo de datos con SQL Server 2012. aunque la instrucción select no requiere control de transaccion en muchas ocasiones se usan tablas temporales para las cuales es necesario capturar el error en caso que se encuentre con problemas.

Creado por @donpandix

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