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

image

Mensaje de Error en la Transacción:

image

 

votaPorMiBlogIvan

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!!!

About these ads
    • Emmanuel Vilchis
    • 22/01/09

    Amigo Tin tin me da gusto ver que ocupas tu tiempo para algo productivo.
    Me latió tu idea de hacer este blog, sigue así amigo vas por buen camino

    • psykrest
    • 23/01/09

    Si mi bue…n amigo esa es la idea contribuir con algunos conocimientos acerca de lo que más me agrada hacer, saludos seguimos en contacto.

    • Juan
    • 11/04/09

    hola, me da un error cuando compruebo la sintaxis.
    es el siguiente: Incorrect sintax near ‘try’ y el otro error es: ERROR_NAME no es un nombre de funcion reconocido.

    • psykrest
    • 13/04/09

    Hola Juan, en que versión de SQL server estas trabajando, debes de asegurarte que sea 2005 o 2008, de lo contrario SQL 2000 no soporta la instrucción TRY – CATCH, de ERROR_NAME no es correcto, escribe ERROR_MESSAGE, verifica que estas escribiendo en el orden correcto las instrucciones, no deberias de tener mayor problema, espero me informes si ya se soluciono el problema, Saludos.

    • erika
    • 21/05/09

    =s, yo tenia el mismo error que juan.. jeje ya lo habia eliminado , pero en realidad desconocia el porque no lo reconocia. Ahora lo se gracias =) , pero… ERROR_MESSAGE sigue sin funcionar…
    trabajo con sql 2000

    mi codigo:

    BEGIN

    SET NOCOUNT ON;
    DECLARE
    @msg AS VARCHAR(100),
    @APE_PAT AS VARCHAR(50),
    @APE_MAT AS VARCHAR(50),
    @NOMBRE AS VARCHAR(30),
    @EMPRESA AS VARCHAR(50),
    @DEPTO AS VARCHAR(50),
    @PUESTO AS VARCHAR(50),
    @TEL_OFI AS VARCHAR(18),
    @TEL_MOV AS VARCHAR(18),
    @CORREO AS VARCHAR(50),
    @COMENT AS NTEXT,
    @USUARIO AS VARCHAR(40),
    @PASSWRD AS VARCHAR(40),
    @FECHALT AS SMALLDATETIME,
    @USU_ROL AS VARCHAR(50)

    Begin TRAN TAGREGAR
    INSERT INTO CC_USUARIO(APEPAT, APEMAT, NOMBRE, EMPRESA, DEPTO, PUESTO, TELOFI, TELMOV, CORREO, COMENT, USUARIO, PASSWRD, FECHALT, USU_ROL) VALUES (@APE_PAT, @APE_MAT, @NOMBRE, @EMPRESA, @DEPTO, @PUESTO, @TEL_OFI, @TEL_MOV, @CORREO, @COMENT, @USUARIO, @PASSWRD, GETDATE(), @USU_ROL)

    SET @msg = ‘El Usuario se registro correctamente.’

    COMMIT TRAN TAGREGAR

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

    End

    END

  1. Bueno recien me inicio en esto de los STP, la pregunta es como defino una insercion donde debo guardar un pedido y su detalle completo?
    Como paso los datos por param en el STP (especialmente el detalle de Pedido)?

    • Angie
    • 24/05/09

    Tengo una consulta:
    Necesito insertar datos en un procedimiento almacenado a una tabla, entre uno de ellos debo insertar el mail, y en el caso de que se ingrese el mismo mail de nuevo el proceso debe rechazarlo, ¿cómo se podría hacer?
    Espero su respuesta, saludos y gracias

    • psykrest
    • 26/05/09

    Hola Angie, lo que me describes lo haces perfectamente con la instrucción “EXISTS”, te dejo un breve ejemplo:

    IF EXISTS ( SELECT id FROM DEMO_USUARIO WHERE email = @email )
    BEGIN
    @msg = ‘El Usuario ya se encuantra registrado!!!’
    END
    ELSE
    BEGIN
    INSERT INTO DEMO_USUARIO () VALUES ()
    @msg = ‘El usuario se registro correctamente.’
    END

    Ten en cuenta que @email es un variable de tipo parámetro (que trae como valor el correo a insertar y validar), el parámetro @msg que para mi punto de vista será de tipo OUTPUT y es el que retorna tu Procedimiento almacenado para indicar al usuario si se realizó la acción o no, espero te sea de ayuda, saludos y suerte.

    • psykrest
    • 26/05/09

    Saludos Flex, he creado un Post donde comparto una manera para planear (estructurar) y crear sus Procedimientos almacenados, te dejo el enlace por que creo que cubre con gran parte de la pregunta que me hiciste.
    [SQL] Como planear un Procedimiento almacenado en SQL Server 2008 (Creación y ejecución).

    • JaMeS
    • 3/06/09

    De mucha utilidad, te agradezco… muy buen post

    • joaan
    • 25/10/09

    oie qiero agregar un dato x default en mi caso la fecha como le ago?

    • Edison
    • 8/02/10

    No he probado aun tus códigos… pero dime se puede conectar SQL 2005 con V BASIC 6.0 ?… y si es posible (creo que si) cual es la solución para agregar datos desde mis caja de textos en la tabla de mi base de datos…

    • ezequiel
    • 4/03/10

    hola queria saber si cuando usas try catch porq ejempo puedo hacer lo siguiente
    begin try




    end try
    if (condicion)

    begin cath
    _



    end catch

    se puede ???

    • pocholo
    • 25/03/10

    hola psykrest, como puedo traer el mensaje a otra aplicacion, por ejemplo visual basic 6.0 estoy utilizando sql server 2000, atento a tus comentarios, gracias

    • lp312
    • 23/04/10

    Muchas gracias por tus aportes al compartir esta informacion

    • psykrest
    • 24/04/10

    Oops, perdon por no contestar antes, mira a mi opinion deberia de utilizar Servicios Web que se conecten a tu Base de datos y a su vez estos mismos servicios se comuniquen con tu aplicación de escritorio, de esta manera puedes enviar datos a la base o recibirlos en tu aplicación a travez de dichos web services, una disculpa por la demora, pero el trabajo no me deja seguir con el blog, saludos

    • psykrest
    • 24/04/10

    Hola, perdon por la demora, pero ya sabes que primero esta el trabajo, lo que comentas claro que se puede hacer, mira:
    dentro de tu bloque:
    Begin Try
    Declare @param1 nvarchar(150)

    IF EXISTS(SELECT * FROM LOQUESEA WHERE id = 1000)
    BEGIN
    SET @param1 = ‘Sí existe’
    END
    ELSE
    BEGIN
    SET @param1 = ‘No existe’
    END

    End Try

    Saludos y disculpas.

    • psykrest
    • 24/04/10

    Opps perdon por no contestar antes, claro que sí se puede conectar, un opción que considero esque la comunicación entre tu aplicación y tu base de datos sea a travez de Servicios Web, de esa manera Anvias datos de los objetos de tu aplicacion al Servicio Web y este se encarga de enviarlos a la base de datos y listo xD.
    Saludos, esta más atento al Blog….

    • Benito Kamelo
    • 17/02/11

    Excelentes ejemplo de SP. AHora tengo una idea mas clara de los que hacen.

    • john
    • 21/09/11

    necesito saber cual es el motivo del error en mi base de datos,ya revise todo y el procedimiento de insertar email me manda error estoy utilizando sql 2008

    • psykrest
    • 12/10/11

    Hola, prueba hechando un vistazo al Monitor de Actividades del SQL Server, Cual es el numero del error o el mensaje?

    Ivan

    • Dirson Andres
    • 31/10/11

    muy bacano … gracias a todos

    • Vanesa Percy
    • 29/11/11

    Hola

    ¿Oye y tu tienes el procedimiento almacenado con transacción para modificar registros?

    Si lo tienes por favor publicalo

    • psykrest
    • 29/11/11

    Hola, Solo reemplaza los INSERTS por los UPDATES que quieres hacer:

    Reemplazar:
    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);
    Por:
    UPDATE dbo.USUARIO_SYS SET nombre = @nom, apellidos = @ape, email = @ema, pass = @pas WHERE id = @id;
    UPDATE dbo.USUARIO_JERARQUIAS_SYS SET id_jerarquia = @newIdJer WHERE id_usuario = @id AND id_jerarquia = @oldIdJer;

    Saludos y suerte….Ivan

    • jimico
    • 9/12/11

    Hola,que tal,estoy empezando con sql,esta nuy bueno el post y el blog.
    una felicitacion.

    • Argelin
    • 12/12/11

    Excelente, me funciono a la perfeccion. Ahora me asigne la tarea de reutilizarlo para modificar y borrar registros.
    Gracias..

    • Argelin
    • 12/12/11

    Me funcionó cambiando los Insert por update para modificar registros..

    Ahora solo me falta, trabajar en los WebForms para EJECUTAR el procedure y pasarle los TextBox a los parametros y los Parametros a los campos..

    Esa tarea no se como la realizare, si alguien tiene un ejemplo..

    Mi correo es:
    argelyn3@hotmail.com

    Gracias de antemano.

    • Jose
    • 19/12/11

    En q lenguaje estas trabajando argelin?

  2. Hola, me gustaria si me pudieras ayudar tengo una tabla ejemplo:

    tbRequisitos
    idReq int
    nom_req nvarchar
    desc_req nvarchar

    y otra tabla

    tbControl_Requisitos
    idCtrol_Req INT
    idActividad INT
    idReq INT
    Statu INT

    quiero copiar los registro que tenga en la tabla tbRequisitos, en la tbControl_Requisito, pero solo idReq int…….

    Se supone que ya existen dato en la tabla tbRequisitos.

    IdReq nom_req desc_req
    1 Acta Nacimeinto Original y Copia
    2 Requsiito 2 Desc2
    3 Requsiito 3 Desc3
    4 Requsiito 4 Desc4
    5 Requsiito 5 Desc5
    6 Requsiito 6 Desc6

    Quiero que cuando se genere un registro en la tbActividades, se llene automaticamente esta tabla con el idActividad (generado) y copie los idReq existentes algo asi como un ciclo y vaya leyendo e insertando pero no se como hacerlo.

    idCtrol_Req idActividad idReq Statu
    11 4 1 7
    12 4 2 7
    13 4 3 7
    14 4 4 7
    15 4 5 7
    16 4 6 7

    • Alex Sanyago
    • 13/03/12

    Exelente blog…!!!

    • loco
    • 22/03/12

    Desde vb.net 2010 estoy llamando a un procedimiento , como hago para que este me coja el error y avise al usuario que se ah producido un error y no se ah llevado a cabo el stored procedure.

    Gracias

    • Ivan
    • 17/05/12

    Buen blog; quisiera que me ayudes, tengo un problema al tratar de ingresar datos en dos tablas relacionadas, pues una de mis tablas tiene un Foreign Key de la otra tabla, lo que quiero es ingresar datos desde un solo formulario a estas tablas relacionadas con un solo procedimiento almacenado, espero que me ayudes.
    y saludos

    • psykrest
    • 17/05/12

    Hola, es muy sencillo, lo que tienes que hacer es crear un Store Procedure, dentro de este deberas de insertar en la tabla principal, recuperar el último ID, he insertar en la segunda table, ie.

    DECLARE @lastId bigint;
    INSERT INTO MyTable1(field1, field2, created) VALUES(‘dato1′, ‘dato2′, getdate());

    — RECUPERAMOS EL ID DEL ÚLTIMO REGISTRO INSERTADO EN MyTable1
    SET @lastId = SCOPE_IDENTITY();

    — PROCEDEMOS A INSERTAR EN LA SIGUIENTE TABLA, QUE CONTIENE LA RELACIÓN (MyTable2), para este caso la FK es “MyTable1ID”;.

    INSERT INTO MyTable2(MyTable1ID, field1, field2, field3) VALUES(@lastId,’dato1′, ‘dato2′, ‘dato3′);

    — LISTO!!!

    • David Cardenas
    • 15/08/12

    Hola felicitaciones por el tuto, una pregunta si quiero mostrar el mensaje de insersion o error en un formulario (pantalla de usuario), como lo haria, estoy buscando esa info y no logro encontrar, q al llamar al sp, obtenga un valor q le diga al usuario si grabo bien o no. mil gracias

    • roy
    • 30/12/12

    oye mil gracias pero nos ayudas a dispararlo desde vb .net

    • joe07
    • 30/01/13

    Amigo buenas tardes desde Mexico, tu post esta increible pero al igual que Roy me ayudarias muchisimo si nos mostraras la forma de ejecutarlo desde VB.net… mil gracias por tu aporte y tu ensenanza, saludos.

    • gifiv
    • 2/02/13

    hola amigos, saludos desde Perú, me encanto el post y es excelente, lo que quisiera es saber como capturar el ,@msg desd c#

    • dfss
    • 6/03/13

    disculpa amigo como llamo a un procedimiento almacenado desde un trigger saludos

    • Pablo
    • 15/05/13

    Buen ejemplo, como complemento hubiera sido mejor el mostrar las tablas.

  3. CREATE PROCEDURE SP_INSERTAPROV
    @PIDPROV VARCHAR(10),
    @PNOMBPROV VARCHAR(20),
    @PDIRPROV VARCHAR(20)
    AS
    BEGIN
    INSERT INTO PROVEEDOR(id_proveedor,nombre_prov,direc_prov)
    VALUES (@PIDPROV,@PNOMBPROV,@PDIRPROV)
    END

    A DONDE UBICO EL ORDER BY ¿? PARA ORDENAR UNA TABLA DE CLIENTES POR SU NOMBRE EN FORMA DESCENDENTE¿?

    • Mauiricio
    • 6/06/13

    Muy bueno, desde CR.

  4. muy buen aporte, pero tengo una consulta: tengo un procedimiento almacenado en sql 2008 este me genera un resultado como puedo mostrar este resultado en un textbox o en un label en visual studio 2010 (asp.net), te agradecería que me ayudaras

    • yeso
    • 9/11/13

    Felicidades por el post, excelente ayuda, pero quisiera hacerte una consulta: cuando ejecuto el sp con un error por ejemplo tratar de introducir un texto en un campo de fecha, el rollback funciona, pero el mensaje de error no se muestra, en los resultados solo aparece null y no el mensaje de :”ocurrio un error….etc.”. cuando el insert se realiza con los datos correctos, si muestra el mensaje de: “El Usuario se registro correctamente.”

  5. GRACIAS.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

A %d blogueros les gusta esto: