Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

IF @@TRANCOUNT > 0 COMMIT TRANSACTION;

Hello Experts;

Need some Expert help with SQL Transactions using TRY, CATCH, COMMIT and ROLLBACK.

Creating a SP that will receive parameters and based on the @DuplicateType will perform 1 of 4 tasks.  If the task is successful COMMIT, else ROLLBACK.

I have attached just the basic SP, but do I have my COMMIT TRANSACTION in the proper place?
COMMIT-TRANS.sql
0
wdbates
Asked:
wdbates
  • 3
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
Yes.

But you really should use XACT_STATE() rather than @@TRANCOUNT.

Also, you need a BEGIN / END block on the IF in the error section:
BEGIN CATCH
       
      IF @@TRANCOUNT > 0
      BEGIN

            SELECT
                  @ErrorMessage   = ERROR_MESSAGE(),
                  @ErrorSeverity  = ERROR_SEVERITY(),
                  @ErrorState     = ERROR_STATE(),
                  @ErrorProcedure = ERROR_PROCEDURE(),
                  @ErrorLine      = ERROR_LINE(),
                  @ErrorNumber    = ERROR_NUMBER();

             ROLLBACK TRANSACTION;
            
             RAISERROR (@ErrorMessage,   -- Message text
                              @ErrorSeverity,  -- Severity
                              @ErrorState,     -- State
                              @ErrorProcedure, -- Procedure
                              @ErrorLine,             -- Line
                              @ErrorNumber     -- Number
                              );
    END
END CATCH;
0
 
Éric MoreauSenior .Net ConsultantCommented:
If you have a "begin tran" and no "commit", the operation will be automatically rolled back so yes you need the commit
0
 
wdbatesAuthor Commented:
Thank you Scott for the answer and the tip on XACT_STATE.  See the test code below where if the @DuplicateType = 1 you will receive a Divide by Zero error.

Thank you again.

BEGIN

SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION

      SET NOCOUNT ON;
      DECLARE @ErrorMessage   NVARCHAR(4000);
    DECLARE @ErrorSeverity  INT;
    DECLARE @ErrorState     INT;
    DECLARE @ErrorProcedure NVARCHAR(128);
    DECLARE @ErrorLine      INT;
    DECLARE @ErrorNumber    INT;
    DECLARE @DuplicateType  INT = 1;
      
      if @DuplicateType = 1 --Duplicate master page to new test page
      begin
            PRINT 'One'
            SELECT 1/0
      end
      
      if @DuplicateType = 2 --Duplicate test page to a new test page.
      begin
            PRINT 'two'
      end
      
      if @DuplicateType = 3 --Duplicate test page to new master page
      begin
            PRINT 'tree'
      end
      
      if @DuplicateType = 4 --Duplicate master page to a new master page.
      begin
            PRINT 'four'
      end
      
      IF (XACT_STATE()) = 1
    BEGIN
        PRINT 'The transaction is committable.' +
              ' Committing transaction.'
        COMMIT TRANSACTION;  
    END;

END TRY

BEGIN CATCH
       
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT 'The transaction is in an uncommittable state.' +
              ' Rolling back transaction.'
            SELECT
                  @ErrorMessage   = ERROR_MESSAGE(),
                  @ErrorSeverity  = ERROR_SEVERITY(),
                  @ErrorState     = ERROR_STATE(),
                  @ErrorProcedure = ERROR_PROCEDURE(),
                  @ErrorLine      = ERROR_LINE(),
                  @ErrorNumber    = ERROR_NUMBER();
                  
        ROLLBACK TRANSACTION;
       
            RAISERROR (@ErrorMessage,   -- Message text
                         @ErrorSeverity,  -- Severity
                         @ErrorState,     -- State
                       @ErrorProcedure, -- Procedure
                       @ErrorLine,             -- Line
                       @ErrorNumber     -- Number
                  );
    END;
   
END CATCH;

END
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Scott PletcherSenior DBACommented:
Looks very good overall.

However, to be thorough, I'd fully check XACT_STATE() in both the TRY area and CATCH areas:

--TRY area
     IF (XACT_STATE()) = 1
     BEGIN
         PRINT 'The transaction is committable.' +
               ' Committing transaction.'
         COMMIT TRANSACTION;  
     END
ELSE IF XACT_STATE() = -1
     BEGIN
         PRINT 'The transaction is committable.' +
               ' Committing transaction.'
         COMMIT TRANSACTION;  
     END;
END TRY
...
BEGIN CATCH
         
     IF (XACT_STATE()) <> 0
     BEGIN
         PRINT 'The transaction had an error.' +
               ' Rolling back transaction.'
    ...
0
 
wdbatesAuthor Commented:
Is this correct?

BEGIN

SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION

      SET NOCOUNT ON;
      DECLARE @ErrorMessage   NVARCHAR(4000);
    DECLARE @ErrorSeverity  INT;
    DECLARE @ErrorState     INT;
    DECLARE @ErrorProcedure NVARCHAR(128);
    DECLARE @ErrorLine      INT;
    DECLARE @ErrorNumber    INT;
    DECLARE @DuplicateType  INT = 1;
      
      if @DuplicateType = 1 --Duplicate master page to new test page
      begin
            PRINT 'One'
            SELECT 1/0
      end
      
      if @DuplicateType = 2 --Duplicate test page to a new test page.
      begin
            PRINT 'two'
      end
      
      if @DuplicateType = 3 --Duplicate test page to new master page
      begin
            PRINT 'tree'
      end
      
      if @DuplicateType = 4 --Duplicate master page to a new master page.
      begin
            PRINT 'four'
      end
      
      IF (XACT_STATE()) = 1
    BEGIN
        PRINT 'The transaction is committable.' +
              ' Committing transaction.'
        COMMIT TRANSACTION;  
    END
    ELSE IF XACT_STATE() = -1
     BEGIN
         PRINT 'The transaction is committable.' +
               ' Committing transaction.'
         COMMIT TRANSACTION;  
     END

END TRY

BEGIN CATCH
       
    IF (XACT_STATE()) <> 0
    BEGIN
        PRINT 'The transaction is in an uncommittable state.' +
              ' Rolling back transaction.'
            SELECT
                  @ErrorMessage   = ERROR_MESSAGE(),
                  @ErrorSeverity  = ERROR_SEVERITY(),
                  @ErrorState     = ERROR_STATE(),
                  @ErrorProcedure = ERROR_PROCEDURE(),
                  @ErrorLine      = ERROR_LINE(),
                  @ErrorNumber    = ERROR_NUMBER();
                  
        ROLLBACK TRANSACTION;
       
            RAISERROR (@ErrorMessage,   -- Message text
                         @ErrorSeverity,  -- Severity
                         @ErrorState,     -- State
                       @ErrorProcedure, -- Procedure
                       @ErrorLine,             -- Line
                       @ErrorNumber     -- Number
                  );
    END;
   
END CATCH;

END
0
 
Scott PletcherSenior DBACommented:
Yep, the TRY part is great.

The CATCH is not quite fully accurate.  Only -1 means "can't be committed".  I also rolled back state 1 because we are in the CATCH block, which means some type of error must have occurred.

BEGIN CATCH
         
     IF (XACT_STATE()) <> 0
     BEGIN
          IF XACT_STATE() = -1
              PRINT 'The transaction is in an uncommittable state.' +
                   ' Rolling back transaction.'
         ELSE --IF XACT_STATE() = 1
              PRINT 'The transaction had an error.' +
                   ' Rolling back transaction.'
             SELECT
                   @ErrorMessage   = ERROR_MESSAGE(),
                   @ErrorSeverity  = ERROR_SEVERITY(),
                   @ErrorState     = ERROR_STATE(),
                   @ErrorProcedure = ERROR_PROCEDURE(),
                   @ErrorLine      = ERROR_LINE(),
                   @ErrorNumber    = ERROR_NUMBER();
                   
         ROLLBACK TRANSACTION;
         
             RAISERROR (@ErrorMessage,   -- Message text
                          @ErrorSeverity,  -- Severity
                          @ErrorState,     -- State
                        @ErrorProcedure, -- Procedure
                        @ErrorLine,             -- Line
                        @ErrorNumber     -- Number
                   );
     END;
     
 END CATCH;

 END
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now