[SQL] Procedimiento almacenado para Insertar datos con transacción y control de errores.

Hola amigos, en muchas ocasiones no sabemos si estamos realizando correctamente un proceso de inserción, actualización o eliminación de datos a una tabla(s), esto para muchos es sumamente sencillo, pero me di a la tarea de preparar un pequeño ejemplo de esto.  El script de T-Sql consiste en realizar un procedimiento almacenado que reciba los datos necesarios para insertarlos en la tabla, para garantizar la ejecución correcta de las inserciones utilizo las transacciones “Transact SQL” y para validar la reversión de la transacción en caso de que ocurra un ERROR utilizo el control de Errores Try – Catch con RollBack.

¿Qué es una Transacción?

Una transacción es un conjunto de operaciones T-SQL que funcionan como un solo bloque de instrucciones, esto significa que si ocurre un fallo durante la ejecución de alguna instrucción del bloque todas las demás fallaran, lo que implica que nada más se ejecutara y la transacción tendrá que deshacer todo lo que había ejecutado hasta donde ocurrió el fallo, a eso se la llama reversión de la transacción y se ejecuta con un ROLLBACK, en caso de una ejecución correcta se tendrá que grabar la transacción con COMMIT, el objetivo de las transacciones es garantizar que los datos que se inserten, actualicen o eliminen queden CONSISTENTES en la base de datos.

Después de una ligera explicación de las Transacciones veremos el siguiente ejemplo de una inserción dentro de un procedimiento almacenado con transacción.

Definiendo las estructuras.

1. La estructura del SP es:

CREATE PROCEDURE nombreProdedimiento
    -- Parámetros del SP
    @Param1 AS Varchar(25),
    @Param2 AS Bigint
    .
    .
    .
AS
BEGIN    

    -- Insertar bloque de instrucciones a ejecutar.
    /*
        SELECT
        UPDATE
        INSERT
        DELETE
        Lo que quieras....
    */
END
GO

 

2. La estructura del control de errores TRY-CATCH es:

Begin Try

    /*Bloque de instrucciones a validar.
    -----------------------------------------
    -----------------------------------------
    -----------------------------------------*/

End Try
Begin Catch
    /*Bloque de instrucciones que se ejecutan si ocurre
    un ERROR.
    -----------------------------------------
    -----------------------------------------
    -----------------------------------------*/
End Catch

3. La estructura de una Transacción es:

Begin Tran NombreTransaccion--Inicio de la transacción con su nombre Tadd o el que elijas.

    /*Bloque de instrucciones a ejecutar en la Transacción
    ---------------------------------------
    ---------------------------------------*/

Commit Tran NombreTransaccion--Confirmación de la transacción.

Rollback Tran NombreTransaccion--Reversión de la transacción.

 

4. Para finalizar veremos todo lo anterior armado ya en conjunto de SP, Transacción y control de Errores.

-- =============================================
-- Author:        Ivan Rangel Cuadros.
-- Create date: 22/Ene/2009
-- Description:    Procedimiento para Insertar registros en una Tabla con transacción y control de errores.
-- =============================================
CREATE PROCEDURE spAgregaUsuario
    @nom AS VARCHAR(50),
    @ape AS VARCHAR(50),
    @ema AS VARCHAR(30),
    @pas AS VARCHAR(20),
    @idJer AS BIGINT,
    @msg AS VARCHAR(100) OUTPUT

AS
BEGIN

    SET NOCOUNT ON;

    Begin Tran Tadd

    Begin Try

        INSERT INTO dbo.USUARIO_SYS (nombre, apellidos, email, pass, fecha_add) VALUES (@nom, @ape, @ema, @pas, GETDATE())

        INSERT INTO dbo.USUARIO_JERARQUIAS_SYS (id_usuario, id_jerarquia) VALUES (@@IDENTITY, @idJer)

        SET @msg = 'El Usuario se registro correctamente.'

        COMMIT TRAN Tadd

    End try
    Begin Catch

        SET @msg = 'Ocurrio un Error: ' + ERROR_MESSAGE() + ' en la línea ' + CONVERT(NVARCHAR(255), ERROR_LINE() ) + '.'
        Rollback TRAN Tadd

    End Catch

END
GO

 

Explicando el procedimiento:

1. Recibe los datos a través de parámetros de los cuales uno es de salida OutPut , que servirá para indicar si la Inserción se realizo correctamente o no.

2. Crea una transacción llamada Tadd, valida con TRY-CATCH las inserciones a las tablas USUARIO y USUARIO_JERARQUIAS.

3. Si las operaciones de inserción se ejecutan correctamente las confirma con COMMIT TRAN Tadd y regresa la variable @msg con un valor de confirmación correcto, si ocurre un error se revierte la transacción con ROLLBACK TRAN Tadd y devuelve en @msg un valor de error.

Ejecutando el Procedimiento:

DECLARE @msg AS VARCHAR(100);
EXEC spAgregaUsuario 'Claudia','Perez Torres','clau@mail.com','a220109',1,@msg OUTPUT
SELECT @msg AS msg

Mensaje de inserción correcta:

Mensaje de Error en la Transacción:

 

Apóyame votando aquí: http://blogit.ms/TopBloggers.aspx en la categoría SQL Server.

Los pasos para votar están aquí te llevara 3 min: Clic Aquí. 

Espero les sea de ayuda. Dejen comentarios!!!

Etiquetas de Technorati: SQL,SQL 2005,Procedimeintos almacenados,Trasacciones,T-SQL,TSQL,Control de errores,Try-Catch,Transact SQL,Store Procedure