Transacción en un procedimiento almacenado

Esta es el arreglo más correcta que te podemos compartir, sin embargo estúdiala detenidamente y analiza si es compatible a tu trabajo.

Solución:

Necesitas envolver ese código en CREATE PROCEDURE ... sintaxis y elimine la GO declaraciones después BEGIN TRANSACTION y antes COMMIT TRANSACTION.

GO
CREATE PROCEDURE dbo.AssignUserToTicket
(
     @updateAuthor varchar(100)
    , @assignedUser varchar(100)
    , @ticketID bigint
)
AS
BEGIN
    BEGIN TRANSACTION;
    SAVE TRANSACTION MySavePoint;
    SET @updateAuthor = 'user1';
    SET @assignedUser = 'user2';
    SET @ticketID = 123456;

    BEGIN TRY
        UPDATE dbo.tblTicket 
        SET ticketAssignedUserSamAccountName = @assignedUser 
        WHERE (ticketID = @ticketID);

        INSERT INTO [dbo].[tblTicketUpdate]
            (
            [ticketID]
            ,[updateDetail]
            ,[updateDateTime]
            ,[userSamAccountName]
            ,[activity]
            )
        VALUES (
            @ticketID
            , 'Assigned ticket to ' + @assignedUser
            , GetDate()
            , @updateAuthor
            , 'Assign'
            );
        COMMIT TRANSACTION 
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION MySavePoint; -- rollback to MySavePoint
        END
    END CATCH
END;
GO

También tenga en cuenta que he agregado un TRY...CATCH bloque de instrucciones para permitir realizar una ROLLBACK TRANSACTION declaración en caso de que ocurra algún error. Probablemente necesite un mejor manejo de errores que eso, pero sin el conocimiento de sus requisitos, eso es difícil en el mejor de los casos.

Buena lectura:

1. Siempre especifique el esquema

2. Mejores prácticas de procedimientos almacenados

3. Malos hábitos para evitar

Si desea manejar correctamente los Procedimientos almacenados anidados que pueden manejar Transacciones (ya sea que se inicien desde T-SQL o el código de la aplicación), debe seguir la plantilla que describí en la siguiente respuesta:

¿Estamos obligados a manejar la transacción en el código C # así como en el procedimiento almacenado?

Notará dos diferencias allí con respecto a lo que está intentando aquí:

1. El uso de RAISERROR dentro de CATCH cuadra. Esto hace que el error suba al nivel de la llamada (ya sea en la base de datos o en la capa de la aplicación) para que se pueda tomar una decisión con respecto al hecho de que ocurrió un error.

2. No SAVE TRANSACTION. Nunca he encontrado un caso para usar esto. Sé que algunas personas lo prefieren, pero en todo lo que he hecho en cualquier lugar en el que he trabajado, la noción de que ocurra un error dentro de cualquiera de los niveles anidados implicaba que cualquier trabajo que ya se había hecho no era válido. Mediante el uso SAVE TRANSACTION solo está volviendo al estado justo antes de que se llame a este procedimiento almacenado, dejando el proceso existente como válido.

   Si quieres más detalles sobre SAVE TRANSACTION, luego eche un vistazo a la información en esta respuesta:

   Cómo revertir cuando se inician 3 procedimientos almacenados desde un procedimiento almacenado

   Otro problema con SAVE TRANSACTION es un matiz de su comportamiento, como se indica en la página de MSDN para SAVE TRANSACTION (énfasis agregado):

   Se permiten nombres duplicados de puntos de guardado en una transacción, pero una instrucción ROLLBACK TRANSACTION que especifica el nombre del punto de guardado solo revertirá la transacción al más reciente GUARDAR TRANSACCIÓN con ese nombre.

   Es decir, debe tener mucho cuidado de asignar a cada punto de guardado de cada procedimiento almacenado un nombre que sea único en todos los puntos de guardado de todos los procedimientos almacenados. Los siguientes ejemplos ilustran este punto.

   Este primer ejemplo muestra lo que sucede cuando reutiliza el nombre del punto de guardado; solo se revierte el punto de guardado del nivel más bajo.

   IF (OBJECT_ID(N'tempdb..#SaveTranTestA') IS NOT NULL)
   BEGIN
       DROP TABLE #SaveTranTestA;
   END;
   CREATE TABLE #SaveTranTestA (SomeVal INT NOT NULL);
   
   BEGIN TRAN; -- start level 1
   SAVE TRANSACTION MySavePoint;
   
   SELECT @@TRANCOUNT AS [TranCount]; -- 1
   
   INSERT INTO #SaveTranTestA (SomeVal) VALUES (100);
   
   BEGIN TRAN; -- start level 2
   SAVE TRANSACTION MySavePoint;
   
   SELECT @@TRANCOUNT AS [TranCount]; -- 2
   
   INSERT INTO #SaveTranTestA (SomeVal) VALUES (200);
   
   COMMIT; -- exit level 2
   
   SELECT @@TRANCOUNT AS [TranCount]; -- 1
   SELECT * FROM #SaveTranTestA;
   -- 100
   -- 200
   
   ROLLBACK TRANSACTION MySavePoint; -- error occurred; undo actions up to this point
   
   SELECT @@TRANCOUNT AS [TranCount]; -- 1
   SELECT * FROM #SaveTranTestA;
   -- 100
   
   COMMIT; -- exit level 1
   
   SELECT @@TRANCOUNT AS [TranCount]; -- 0
   SELECT * FROM #SaveTranTestA;
   -- 100
   

   Este segundo ejemplo muestra lo que sucede cuando utiliza nombres únicos de puntos de guardado; el punto de guardado del nivel deseado se revierte.

   IF (OBJECT_ID(N'tempdb..#SaveTranTestB') IS NOT NULL)
   BEGIN
       DROP TABLE #SaveTranTestB;
   END;
   CREATE TABLE #SaveTranTestB (SomeVal INT NOT NULL);
   
   BEGIN TRAN; -- start level 1
   SAVE TRANSACTION MySavePointUno;
   
   SELECT @@TRANCOUNT AS [TranCount]; -- 1
   
   INSERT INTO #SaveTranTestB (SomeVal) VALUES (100);
   
   BEGIN TRAN; -- start level 2
   SAVE TRANSACTION MySavePointDos;
   
   SELECT @@TRANCOUNT AS [TranCount]; -- 2
   
   INSERT INTO #SaveTranTestB (SomeVal) VALUES (200);
   
   COMMIT; -- exit level 2
   
   SELECT @@TRANCOUNT AS [TranCount]; -- 1
   SELECT * FROM #SaveTranTestB;
   -- 100
   -- 200
   
   ROLLBACK TRANSACTION MySavePointUno; --error occurred; undo actions up to this point
   
   SELECT @@TRANCOUNT AS [TranCount]; -- 1
   SELECT * FROM #SaveTranTestB;
   -- 
   
   COMMIT; -- exit level 1
   
   SELECT @@TRANCOUNT AS [TranCount]; -- 0
   SELECT * FROM #SaveTranTestB;
   -- 
   

Si estás de acuerdo, puedes dejar un enunciado acerca de qué te ha parecido esta crónica.

---

---

Utiliza Nuestro Buscador