SQL Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

Question

I am working on an Enterprise Database Management course project and cannot seem to get my error handling to perform correctly on a transaction. My instructor advised me to take a look at the error "Msg 266, Level 16, State 2, Procedure pInsertVolunteer, Line 28 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. " but I have not yet found any solutions that are working for this transaction.

CREATE PROCEDURE [dbo].[pInsertVolunteer]
    @LastName varchar(255),
    @FirstName varchar(255),
    @DateOfBirth date,
    @Note varchar(MAX),
    @ModifiedBy nvarchar(50),
    @Success bit Output,
    @ErrorStatus Nvarchar(50) Output

 AS
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    BEGIN
        DECLARE
        @TempVolunteerID int,
        @VolunteerID int,
        @VolunteerPersonID int;

    SET @TempVolunteerID = 0;
    SET @Success = 0;
    SET @ErrorStatus = '';

END
BEGIN TRY
    BEGIN TRANSACTION;
    SELECT @TempVolunteerID = VolunteerID
    FROM Volunteer.VolunteerInfo
        WHERE FirstName = @FirstName AND LastName = @LastName AND DateOfBirth = @DateOfBirth
        If @TempVolunteerID <>0
            BEGIN
                SELECT @ErrorStatus = 'Person Already Exists';
                SET @Success = 0;
                RAISERROR (@ErrorStatus, 16,1);
            END
        IF @@ERROR <>0
            BEGIN
                SELECT @ErrorStatus = CONVERT (nvarchar(50),@@ERROR) + '-1000';
                SET @Success = 0;
                RAISERROR(@ErrorStatus, 16,1);
            END
    
    INSERT INTO Volunteer.VolunteerInfo (LastName, FirstName, DateOfBirth)
    values (@LastName, @FirstName, @DateofBirth)
            SET @Success = 1;
            SET @ErrorStatus = 0;

    SELECT @VolunteerPersonID = VolunteerID
    FROM Volunteer.VolunteerInfo
    If @VolunteerPersonID = 0 OR @VolunteerPersonID IS NULL
            BEGIN
                    SET @Success =0;
                    SET @ErrorStatus = '8008: PersonID must be valid';
                        
                    RAISERROR(@ErrorStatus,16,1);
            END

    Insert into Volunteer.VolunteerNotes (Note,ModifiedDate, ModifiedBy)
    Values (@Note,SYSDATETIME(), @ModifiedBy)

            SET @VolunteerPersonID = @@IDENTITY;
            SET @Success = 1;
            SET @ErrorStatus = 0;
            SET NOCOUNT OFF;
        
            COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
        
            IF @@TRANCOUNT > 0
                BEGIN
                    SELECT @ErrorStatus = @ErrorStatus + CONVERT (nvarchar(50), @@ERROR);
                END
                    SET @Success = 0;
                    SET @ErrorStatus = @ErrorStatus + '-1001';
                    RAISERROR(@ErrorStatus, 15,1)
            ROLLBACK TRANSACTION;
            
        END CATCH       
GO

Any help would be greatly appreciated.

Thank you!

Formatting your code and using indentation carefully will help others immensely understand it. But you don't seem to understand try/catch. You have IF @@ERROR <>0 after the first SELECT statement. That is logical nonsense and will never be reached. Any exception will be caught in a catch block (if it exists). No idea why you need to catch errors only to re-raise them and set an output parameter - but maybe you have very atypical requirements. Generally you shouldn't be trying to undermine the exception logic by using your own. — SMor
You should really be using THROW too, as the documentation on RAISERROR tells you. — Larnu
And it seems you try to "manually" enforce a natural key (name, dob). Don't - that should be enforced with a unique constraint. In addition, you should be using THROW rather than RAISERROR. Lastly - read the doc about XACT_ABORT. Searching about @@IDENTITY and why that might be a bad idea as well. — SMor
Lastly, Erland has a good discussion of error handling - it is not a straight-forward as one might think. Bookmark his site as it is full of useful information. — SMor
Are you executing the proc directly from an SSMS query window or some other method? You will get that error if a transaction is open before the proc call an the proc rolls back the transaction after an error. — Dan Guzman

SQLpro SQLpro · Accepted Answer · 2021-02-22T15:22:44

Please try this way :

CREATE PROCEDURE [dbo].[pInsertVolunteer] @LastName    VARCHAR(255), 
                                          @FirstName   VARCHAR(255), 
                                          @DateOfBirth DATE, 
                                          @Note        VARCHAR(MAX), 
                                          @ModifiedBy  NVARCHAR(50), 
                                          @Success     BIT OUTPUT, 
                                          @ErrorStatus NVARCHAR(50) OUTPUT
AS
-- settings
SET NOCOUNT ON;
SET XACT_ABORT ON;
-- variables
DECLARE @TempVolunteerID INT = 0, @VolunteerID INT, @VolunteerPersonID INT;
SET @Success = 0;
SET @ErrorStatus = '';

-- controled transaction
BEGIN TRY
   BEGIN TRANSACTION;
   SELECT @TempVolunteerID = VolunteerID
   FROM Volunteer.VolunteerInfo
   WHERE FirstName = @FirstName
         AND LastName = @LastName
         AND DateOfBirth = @DateOfBirth;
   IF @TempVolunteerID <> 0
   BEGIN
      SELECT @ErrorStatus = 'Person Already Exists';
      SET @Success = 0;
      RAISERROR(@ErrorStatus, 16, 1);
   END;
   IF @@ERROR <> 0
   BEGIN
      SELECT @ErrorStatus = CONVERT(NVARCHAR(50), @@ERROR) + '-1000';
      SET @Success = 0;
      RAISERROR(@ErrorStatus, 16, 1);
   END;
   INSERT INTO Volunteer.VolunteerInfo
      (LastName, 
       FirstName, 
       DateOfBirth
      )
   VALUES
      (@LastName, 
       @FirstName, 
       @DateofBirth
      );
   SET @Success = 1;
   SET @ErrorStatus = 0;
   SELECT @VolunteerPersonID = VolunteerID
   FROM Volunteer.VolunteerInfo;
   IF @VolunteerPersonID = 0
      OR @VolunteerPersonID IS NULL
   BEGIN
      SET @Success = 0;
      SET @ErrorStatus = '8008: PersonID must be valid';
      RAISERROR(@ErrorStatus, 16, 1);
   END;
   INSERT INTO Volunteer.VolunteerNotes
      (Note, 
       ModifiedDate, 
       ModifiedBy
      )
   VALUES
      (@Note, 
       SYSDATETIME(), 
       @ModifiedBy
      );
   SET @VolunteerPersonID = @@IDENTITY;
   SET @Success = 1;
   SET @ErrorStatus = 0;
   SET NOCOUNT OFF;
   COMMIT TRANSACTION;
END TRY
BEGIN CATCH
   IF XACT_STATE() <> 0
   BEGIN
      ROLLBACK;
      SELECT @ErrorStatus = @ErrorStatus + CONVERT(NVARCHAR(50), ERROR_MESSAGE());
   END;
   SET @Success = 0;
   SET @ErrorStatus = @ErrorStatus + '-1001';
   RAISERROR(@ErrorStatus, 15, 1);
END CATCH;       
GO

SQL Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

1 Answers