PROCEDIMIENTOS ALMACENADOS

 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  CREATE PROCEDURE [dbo].[Objeto_Accion]