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

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. 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: