Problem in stored procedure getting mismatch error

Question

Can anybody please tell me what the issue is with my code? I have tried for a long time but the code is just not working.

The Select statement is not getting executed and throws an error:

Msg 266, Level 16, State 2, Procedure HW5INS2, Line 14
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

What to do?

ALTER PROCEDURE HW5INS2
    -----LEVEL-2:ShipmentBoxes
    @boxID VARCHAR(16),
    @boxWeight NUMERIC(6,2),
    @boxHeight NUMERIC(5,2),
    @boxWidth NUMERIC(5,2),
    @boxLength NUMERIC(5,2),
    @packager SMALLINT,
    @shipDate SMALLDATETIME ='2018-03-02'  ----NOT REQUIRED
AS
BEGIN TRANSACTION
    ---------Test For Existence Of Parent Row: LOOK INTO Employees TABLE
    IF EXISTS (SELECT employeeID FROM Employees WHERE employeeID = @packager)
    BEGIN
        BEGIN TRANSACTION
        BEGIN TRY
            INSERT INTO ShipmentBoxes (boxID, boxWeight, boxHeight, boxWidth, boxLength, packager, shipDate)
            VALUES (@boxID, @boxWeight, @boxHeight, @boxWidth, @boxLength, @packager, @shipDate)

            SELECT * FROM ShipmentBoxes

            COMMIT
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION

            PRINT 'Error occurred while trying to insert the ShipmentBoxes table'
            SELECT
                ERROR_NUMBER() as errornumber,
                XACT_state() as xstate
        END CATCH
    END
    ELSE
    BEGIN
        -----PARENT DOESNOT EXIST, RETURN ERROR
        ROLLBACK TRANSACTION
        PRINT 'Error: PARENT does not exist'
    END

    RETURN

Please check https://stackoverflow.com/questions/21930156/transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-comm ? — BJohn, Apr 27 '19 at 22:10
Remove `ROLLBACK TRANSACTION` in the `else` part. Transaction starts only `if exists(...)`. — Alex Kudryashev, Apr 28 '19 at 00:16

score -1 · Accepted Answer · answered Apr 28 '19 at 00:17

The message is clear, one or more of your BEGIN/END blocks does not have a close statement END. Also, it'll be a better idea to keep everything inside the TRY/CATCH block to make sure you'll catch all errors and use only one COMMIT/ROLLBACK statements (no redundancy). in the below revision, you can use RAISERROR to rollback the transaction and show the error.

ALTER PROCEDURE HW5INS2
    @boxID varchar(16),
    @boxWeight numeric(6,2),
    @boxHeight numeric(5,2),
    @boxWidth numeric(5,2),
    @boxLength numeric(5,2),
    @packager smallint,
    @shipDate smalldatetime ='2018-03-02'  ----NOT REQUIRED
AS
BEGIN

    BEGIN TRY

        BEGIN TRANSACTION

            IF EXISTS(SELECT employeeID FROM Employees WHERE employeeID = @packager)
            BEGIN

                INSERT INTO ShipmentBoxes (boxID, boxWeight, boxHeight, boxWidth, boxLength, packager, shipDate)
                VALUES (@boxID, @boxWeight, @boxHeight, @boxWidth, @boxLength, @packager, @shipDate)

                SELECT * FROM ShipmentBoxes

                COMMIT  

            END
            ELSE
            BEGIN
              -- USE RAISERROR() TO CATCH THE ERROR AND ROLLBACK TRANSACTION. 
              RAISERROR('Error: PARENT DOESNOT EXIST',16,1)           

            END

    END TRY
    BEGIN CATCH

        ROLLBACK TRANSACTION

        PRINT 'Error Occured While Trying To Insert The ShipmentBoxes Table'
        SELECT
            ERROR_MESSAGE() as ErrorMessage, -- To Display the RAISERROR Message
            ERROR_NUMBER() as errornumber,
            XACT_state() as xstate

    END CATCH

END

Problem in stored procedure getting mismatch error

1 Answers1