sql server - transacciones - Transacción de procedimiento almacenado

transaction block sql

Question

Nunca he usado Transaction, Commit y Rollback antes y ahora necesito usar uno. He comprobado algunos ejemplos en línea, etc. para asegurarme de que, de hecho, estoy usando esto correctamente, pero todavía no estoy seguro de haberlo codificado correctamente. Espero que alguien pueda revisar y aconsejarme si esto parece correcto.

Básicamente tengo 2 bases de datos para una aplicación. Uno es un archivo, lo que significa que los datos que ya no serán manipulados por los usuarios se moverán a esta base de datos. Pero en caso de que alguna vez lo necesiten, transferiré los datos necesarios a la base de datos principal para su uso. Mi proc almacenado está abajo:

CREATE PROCEDURE [dbo].[spReopenClosed] 
(
    @Return_Message VARCHAR(1024) = ''''  OUT,        
    @IID        uniqueidentifier,
    @OpenDate   smalldatetime,
    @ReopenedBy uniqueidentifier
)
AS
BEGIN
    SET NOCOUNT ON;

/******************************
*  Variable Declarations
*******************************/
    DECLARE     @ErrorCode  int  



/******************************
*  Initialize Variables
*******************************/

    SELECT @ErrorCode = @@ERROR

    IF @ErrorCode = 0

    BEGIN TRANSACTION
        /****************************************************************************
        * Step 1
        * Copy the Closed from the Archive
        ****************************************************************************/
        INSERT INTO OPS.dbo.SM_T_In
        SELECT          
        FROM OPS_ARCHIVE.Archive.SM_T_In    W
        WHERE W.GUID = @IID
            AND W.OpenDate = @OpenDate


        IF @ErrorCode <> 0
            BEGIN
                -- Rollback the Transaction
                ROLLBACK

                RAISERROR (''Error in Copying from the archive'', 16, 1)
                RETURN
            END


        /****************************************************************************
        * Step 2
        * copy the notes
        ****************************************************************************/
        INSERT INTO OPS.dbo.SM_T_Notes
        SELECT 
        FROM OPS_ARCHIVE.Archive.SM_T_Notes W
        WHERE W.GUID = @IID

        IF @ErrorCode <> 0
            BEGIN
                -- Rollback the Transaction
                ROLLBACK

                RAISERROR (''Error in copying the notes'', 16, 1)
                RETURN
            END

        /****************************************************************************
        * Step 3
        * Delete the from the Archive - this will also delete the notes
        ****************************************************************************/
        DELETE
        FROM OPS_ARCHIVE.Archive.SM_T_In
        WHERE OPS_ARCHIVE.Archive.SM_T_In.GUID = @IID

        IF @ErrorCode <> 0
            BEGIN
                -- Rollback the Transaction
                ROLLBACK

                RAISERROR (''Error in deleting the items from the Archive'', 16, 1)
                RETURN
            END

        COMMIT

        BEGIN
            SELECT  @ErrorCode  = @@ERROR

            IF @ErrorCode = 0
                    SELECT  @Return_Message = ''All data was moved over''
        END



/*************************************
*  Get the Error Message for @@Error
*************************************/
    IF @ErrorCode <> 0
    BEGIN
        SELECT  @Return_Message = [Description]     -- Return the SQL Server error
          FROM  master.dbo.SYSMESSAGES
         WHERE  error = @ErrorCode
    END

/*************************************
*  Return from the Stored Procedure
*************************************/
    RETURN @ErrorCode                               -- =0 if success,  <>0 if failure
END

Tengo dos inserciones que mueven los datos de 2 tablas de la base de datos de archivo. Si esas inserciones son exitosas, entonces eliminaré los datos de la base de datos de archivo. Apreciaría cualquier comentario sobre esto, debo asegurarme de que lo estoy haciendo correctamente.

Gracias

Answer 1

Bueno, reescribo rápidamente su SP utilizando el concepto TRY CATCH y la TRANSACCIÓN que solicitó, pero no lo verifiqué.

Este código funcionará en SQL 2005/2008

Déjame saber si este comentario puede ser útil para ti.

CREATE PROCEDURE [dbo].[spReopenClosed] 
(
    @Return_Message VARCHAR(1024) = ''''  OUT,        
    @IID        uniqueidentifier,
    @OpenDate   smalldatetime,
    @ReopenedBy uniqueidentifier
)
AS

    SET NOCOUNT ON;

/******************************
*  Variable Declarations
*******************************/
    DECLARE     @ErrorCode  int  
    DECLARE     @ErrorStep  varchar(200)

/******************************
*  Initialize Variables
*******************************/

    SELECT @ErrorCode = @@ERROR

BEGIN TRY

    BEGIN TRAN
        /****************************************************************************
        * Step 1
        * Copy the Closed from the Archive
        ****************************************************************************/

        SELECT @ErrorStep = ''Error in Copying from the archive'';

        INSERT INTO OPS.dbo.SM_T_In
        SELECT *         
        FROM OPS_ARCHIVE.Archive.SM_T_In
        WHERE GUID = @IID
            AND W.OpenDate = @OpenDate


        /****************************************************************************
        * Step 2
        * copy the notes
        ****************************************************************************/

        SELECT @ErrorStep = ''Error in copying the notes''

        INSERT INTO OPS.dbo.SM_T_Notes
        SELECT *
        FROM OPS_ARCHIVE.Archive.SM_T_Notes
        WHERE GUID = @IID

        /****************************************************************************
        * Step 3
        * Delete the from the Archive - this will also delete the notes
        ****************************************************************************/

        SELECT @ErrorStep = ''Error in deleting the items from the Archive''

        DELETE
        FROM OPS_ARCHIVE.Archive.SM_T_In
        WHERE OPS_ARCHIVE.Archive.SM_T_In.GUID = @IID

    COMMIT TRAN

    SELECT  @ErrorCode  = 0, @Return_Message = ''All data was moved over''

    /*************************************
    *  Return from the Stored Procedure
    *************************************/
    RETURN @ErrorCode                               -- =0 if success,  <>0 if failure

END TRY

BEGIN CATCH
    /*************************************
    *  Get the Error Message for @@Error
    *************************************/
    IF @@TRANCOUNT > 0 ROLLBACK

    SELECT @ErrorCode = ERROR_NUMBER()
        , @Return_Message = @ErrorStep + '' ''
        + cast(ERROR_NUMBER() as varchar(20)) + '' line: ''
        + cast(ERROR_LINE() as varchar(20)) + '' '' 
        + ERROR_MESSAGE() + '' > '' 
        + ERROR_PROCEDURE()

    /*************************************
    *  Return from the Stored Procedure
    *************************************/
    RETURN @ErrorCode                               -- =0 if success,  <>0 if failure

END CATCH

Answer 2

Primero, las bases de datos son bastante confiables. Y si fallan, tiene un problema más grande que manejar transacciones individuales. Por lo tanto, mi opinión es que tiene demasiados errores en la comprobación de una transacción simple. Un inserto defectuoso es un evento tan inusual que normalmente no escribiría código para manejarlo.

Segundo, este código no en realidad "captura" los errores:

IF @ErrorCode <> 0

Un error en la declaración SQL abortará el procedimiento almacenado y regresará al cliente. Tendría que intentar ... capturar para manejar realmente un error en un procedimiento almacenado.

En tercer lugar, trato de evitar raiserr . Puede hacer cosas inesperadas tanto en el servidor como en el lado del cliente. En su lugar, considere usar un parámetro de output para devolver información de error al programa cliente.