Procedimientos Almacenados en SQL Server 2005/2008. Control de Errores & Transacciones. Uso XACT_ABORT & XACT_STATE.

A continuación comparto la plantilla de código para la creación de procedimientos almacenados en SQL Server 2005/2008 con control de transacciones que utilizamos en la empresa:

 

 

CREATE PROCEDURE [dbo].[Objeto_Accion]
    -- Parámetros de salida
    [<@paramOUT> int OUTPUT[, <@paramOUTMSG> varchar(255) OUTPUT]
 
    -- Parámetros de entrada (obligatorios)
    <@param1> <datatype>,
    <@param2> <datatype>,
    ...
 
    -- Parámetros de entrada (opcionales)
    <@param11> <datatype> = <default value>,
    <@param12> <datatype> = <default value>,
    ...

AS

 
-- Bloque de declaración de variables

DECLARE <@ValorDevuelto> int
[DECLARE <@MensajeDevuelto> varchar(255)]
 
-- Bloque de declaración de constantes
DECLARE <@ValorDevuelto> int
 
BEGIN


    -- Bloque de sentencias SELECT 
        -- Asignación de variables.
        -- Validaciones. 

        -- Condición de salida previa
        [IF <condición de salida previa> 
            BEGIN
                SET <@ValorDevuelto> = -1
                [SET <@MensajeDevuelto> = 'Detalle de condición de salida']

                [SET <@paramOUT> = <@ValorDevuelto>]
                [SET <@paramOUTMSG> = <@MensajeDevuelto>]

                SELECT <@ValorDevuelto>[, <@MensajeDevuelto>]
                RAISEERROR (50001, 25, ‘Salida sin ejecución’)


                -- Salida sin ejecución
                RETURN(-1)
            END …]
 
    [SET TRANSACTION ISOLATION LEVEL ]
    SET XACT_ABORT ON


    BEGIN TRY


        BEGIN TRANSACTION


            -- Bloque de sentencias DML
                -- ...
                -- ...

                -- Recuperar el ID que nos interesa devolver a la aplicación
                SET <@ValorDevuelto> = SCOPE_IDENTITY()



            -- Bloque de auditoria
                SET ...

                INSERT INTO Auditoria



        COMMIT TRANSACTION


        [SET <@MensajeDevuelto> = 'Mensaje de ejecución OK']

        [SET <@paramOUT> = <@ValorDevuelto>]
        [SET <@paramOUTMSG> = <@MensajeDevuelto>]

        SELECT <@ValorDevuelto>[, <@MensajeDevuelto>]


        -- Salida con ejecución exitosa
        RETURN (1)

    END TRY


    BEGIN CATCH


        -- Obtener info del error. Escribir en tabla de track de Errores
        SET @ERROR_NUMBER = ERROR_NUMBER()
        SET @ERROR_SEVERITY = ERROR_SEVERITY()
        SET @ERROR_STATE = ERROR_STATE()
        SET @ERROR_LINE = ERROR_LINE()
        SET @ERROR_PROCEDURE = ERROR_PROCEDURE()
        SET @ERROR_MESSAGE = ERROR_MESSAGE()
        -- Test XACT_STATE()
            -- If  1, la XACT es "committable". COMMIT
            -- If -1, la XACT es "uncommittable". ROLLBACK
            -- If  0, no hay XACT abierta.

        IF (XACT_STATE()) = -1
            ROLLBACK TRANSACTION

        IF (XACT_STATE()) = 1
            COMMIT TRANSACTION
  -- Ante error SQL se devuelve a la aplicación el ID del registro conteniendo el error en la tabla ErrorInfo
  -- INSERT INTO ErrorInfo
  --     (ErrorNumber, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure, ErrorMessage)
  --     VALUES
  --         (@ERROR_NUMBER, @ERROR_SEVERITY, @ERROR_STATE, @ERROR_LINE, @ERROR_PROCEDURE, @ERROR_MESSAGE)

   -- RAISEERROR (@ERROR_NUMBER, @ERROR_SEVERITY, @ERROR_MESSAGE);

 

 

        SET <@ValorDevuelto> = SCOPE_IDENTITY()
        [SET <@MensajeDevuelto> = {'Detalle del Error' | @ERROR_MESSAGE | ...}]

        [SET <@paramOUT> = <@ValorDevuelto>]
        [SET <@paramOUTMSG> = <@MensajeDevuelto>]
        SELECT <@ValorDevuelto>[, <@MensajeDevuelto>]

        

        -- Salida con error
        RETURN (0)    

    END CATCH

END 
GO
 

Paso a explicar en detalle las secciones más importantes de la plantilla.

 

Parámetros de salida

[<@paramOUT> OUTPUT[, <@paramOUTMSG> OUTPUT]] 

Si bien figura como un bloque opcional, es aconsejable devolver tanto el valor de salida del procedimiento como el mensaje explicativo de éxito o error como parámetros de salida además de como parte del SELECT de resultados.

Bloque de sentencias SELECT

En este bloque deben incluirse todas las asignaciones de variables de trabajo que puedan consultarse en forma independiente a la transacción. De esta manera se reduce el tiempo de bloqueos exclusivos requerido por el procedimiento.

Si existen variables cuyos valores influyen directamente en la transacción deberán asignarse dentro del bloque BEGIN TRANSACTION … END TRANSACTION configurando para ello el nivel de aislamiento deseado previo al mismo:

[SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE | SNAPSHOT}]

 

Condición de salida previa

Si el procedimiento debe incluir algunas verificaciones para detectar casos que impidan la ejecución del mismo, las mismas deben programarse de acuerdo al esquema propuesto en la plantilla. En este caso <@ValorDevuelto> = -1 y <@MensajeDevuelto> puede contener un detalle de porque se abandonó el procedimiento sin ejecutarlo.

El valor de retorno puede ser 0 ó –1. Este último caso permitiría diferenciar el estado de ejecución del procedimiento:

  • “1” indicaría ejecución correcta.
  • “<1” indicaría ejecución incorrecta.
    • “0” ejecución con error.
    • “-1” no ejecución por pre-condición no cumplida.

 

SET XACT_ABORT ON

El uso de esta sentencia ha generado mucha discusión en nuestras reuniones de capacitación. Finalmente concluimos que este valor es indispensable configurar previo a la ejecución todo bloque BEGIN TRY / BEGIN TRANSACTION … COMMIT TRANSACTION / END TRY.

La explicación más exacta del por qué el uso de esta configuración, es porque su utilización permite que cualquier error que se produzca dentro del bloque BEGIN TRY … END TRY marque como inválida a la transacción.

 

Bloque de sentencias DML

Siempre dentro del bloque BEGIN TRY / BEGIN TRANSACTION … COMMIT TRANSACTION / END TRY.

 

Bloque de auditoria

Si no hay implementado algún otro mecanismo de auditoria (aplicación, desencadenadores, etc.) se lo debe incluir dentro del bloque BEGIN TRY / BEGIN TRANSACTION … COMMIT TRANSACTION / END TRY.

 

Bloque BEGIN CATCH… END CATCH

Captura errores producidos dentro del bloque BEGIN TRY … END TRY y confirma o deshace la transacción de acuerdo al valor de la función XACT_STATE().

 

 

Plantilla Simple con devolución de errores únicamente por RETURN.

 

CREATE PROCEDURE [dbo].[Objeto_Accion]
    -- Parámetros de entrada (obligatorios)
    <@param1> <datatype>,
    <@param2> <datatype>,
    ... 
    -- Parámetros de entrada (opcionales)
    <@param11> <datatype> = <default value>,
    <@param12> <datatype> = <default value>,
    ...

AS

 
-- Bloque de declaración de variables

-- Bloque de declaración de constantes
 
BEGIN


    -- Bloque de sentencias SELECT 
        -- Asignación de variables, validaciones, etc.

    SET XACT_ABORT ON

    BEGIN TRY

        BEGIN TRANSACTION

            -- Bloque de sentencias DML
            -- Bloque de auditoria
        COMMIT TRANSACTION

        -- Salida con ejecución exitosa
        RETURN (1)

    END TRY


    BEGIN CATCH
        -- Test XACT_STATE()
        IF (XACT_STATE()) = -1
            ROLLBACK TRANSACTION

        IF (XACT_STATE()) = 1
            COMMIT TRANSACTION

        -- Salida con error
        RETURN (0)    

    END CATCH

END 
GO
 
Published 12-11-2010 11:13 por horaison
Archivado en:
Ofrecido por Community Server (Non-Commercial Edition)