Stored Procedure Transaction

I have never used a Transaction, Commit and Rollback before and now I need to use one. I have checked around online, etc for examples to make sure that I am in fact using this correctly but I am still not sure if I have coded this correct. I am hoping someone can review and advise me if this seems correct.

Basically I have 2 databases for an application. One is an archive – meaning data that is no longer going to be manipulated by the users will be moved to this DB. But in the event they ever need it, I will move the needed data back to the main database for use. My stored proc is below:

  • TSQL NOLOCK VIEW and stored procedure
  • SQL Server : query correlation from two tables
  • What is the best way to fetch records batch-wise from SQL Server
  • How do I create an index inside a stored procedure?
  • Deleting 1 millions rows in SQL Server
  • Build queries from table/column names stored in a table
  • 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
    

    I have two inserts that move the data from 2 tables from the Archive database. If those inserts are successful, then I will delete the data from the Archive DB. I would appreciate any feedback on this, I need to make sure that I am doing this properly.

    Thanks

  • sql transaction don't roll back
  • 2 Solutions collect form web for “Stored Procedure Transaction”

    Oh well i rewrite quickly your SP using the concept TRY CATCH and the TRANSACTION as you requested but i didnt check it.

    This code will work in SQL 2005/2008

    Let me know if this feedback can be useful for you

    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
    

    First, databases are fairly reliable. And if they fail, you have a bigger problem than handling individual transactions. So my feedback would be that you have too much error checking for a simple transaction. A failing insert is such an unusual event that you normally wouldn’t write code to handle it.

    Second, this code won’t actually “catch” errors:

    IF @ErrorCode <> 0
    

    An error in the SQL statement will abort the stored procedure and return to the client. You’d have to try … catch to actually handle an error in a stored procedure.

    Third, I try to avoid raiserr. It can do unexpected things both on the server and the client side. Instead, consider using an output parameter to return error information to the client program.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.