ERROR

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

I get this error in my Stored Procedure that I cannot see where.

ALTER PROCEDURE [dbo].[UpsertServiceTicket]
    @TransactionType INT,
    @Id INT = NULL,
    @CreationDateTime DATETIME = GETDATE,
    @Issue VARCHAR(MAX),
    @ReportedDateTime DATETIME = NULL,
    @ResolutinoDateTime DATETIME = NULL,
    @CreatedBy INT = NULL,
    @ServiceRequestNumber NVARCHAR(MAX),
    @Status VARCHAR(MAX) = NULL,
    @IsDeleted BIT = 0,
    @LocationId INT,
    @SubLocationId INT,
    @RequestorId INT,
    @ConfirmedBy INT = NULL,
    @DepartmentId INT,
    @ErrorMessage NVARCHAR(1000) OUTPUT,
    @ErrorCode SMALLINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION

            IF (@TransactionType = 0)
                INSERT INTO ServiceTickets (
                    CreationDateTime
                    , Issue 
                    , ReportedDateTime
                    , ResolutionDateTime
                    , CreatedBy
                    , ServiceRequestNumber
                    , [Status]
                    , IsDeleted
                    , LocationId
                    , SubLocationId
                    , RequestorId
                    , ConfirmerId
                    , DepartmentId
                    )
                VALUES (
                    @CreationDateTime
                    , @Issue
                    , @ReportedDateTime
                    , @ResolutinoDateTime
                    , @CreatedBy
                    , @ServiceRequestNumber
                    , @Status
                    , @IsDeleted
                    , @LocationId
                    , @SubLocationId
                    , @RequestorId
                    , @ConfirmedBy
                    , @DepartmentId
                )
        -- updating the service ticket table
            ELSE
                UPDATE      ServiceTickets
                SET         CreationDateTime        = @CreationDateTime
                            , Issue                 = @Issue
                            , ReportedDateTime      = @ReportedDateTime
                            , ResolutionDateTime    = @ResolutinoDateTime
                            , CreatedBy             = @CreatedBy
                            , ServiceRequestNumber  = @ServiceRequestNumber
                            , [Status]              = @Status
                            , IsDeleted             = @IsDeleted
                            , LocationId            = @LocationId
                            , SubLocationId         = @SubLocationId
                            , RequestorId           = @RequestorId
                            , ConfirmerId           = ConfirmerId
                            , DepartmentId          = DepartmentId
                WHERE Id = @Id
 
            SET @ErrorMessage = ERROR_MESSAGE()
            SET @ErrorCode = @@ERROR
            IF (@ErrorCode = 0)
                COMMIT TRANSACTION
        END TRY
    BEGIN CATCH
        SET @ErrorMessage = ERROR_MESSAGE()
        SET @ErrorCode = @@ERROR;
  
        IF @ErrorCode > 0
          ROLLBACK TRANSACTION
    END CATCH
END

CodePudding user response：

Your error handling code is incorrect, as it does not rollback or commit the transaction in certain cases

It also swallows exceptions and instead returns an error code. You are far better off just letting the error bubble up back to the client. You should use SET XACT_ABORT ON to ensure a rollback.

Given that you are only ever executing one statement, you don't even need a transaction anyway

ALTER PROCEDURE [dbo].[UpsertServiceTicket]
    @TransactionType INT,
    @Id INT = NULL,
    @CreationDateTime DATETIME = NULL,
    @Issue VARCHAR(MAX),
    @ReportedDateTime DATETIME = NULL,
    @ResolutinoDateTime DATETIME = NULL,
    @CreatedBy INT = NULL,
    @ServiceRequestNumber NVARCHAR(MAX),
    @Status VARCHAR(MAX) = NULL,
    @IsDeleted BIT = 0,
    @LocationId INT,
    @SubLocationId INT,
    @RequestorId INT,
    @ConfirmedBy INT = NULL,
    @DepartmentId INT
AS

    SET NOCOUNT, XACT_ABORT ON;

    SET @CreationDateTime = ISNULL(@CreationDateTime, GETDATE());

    IF (@TransactionType = 0)
        INSERT INTO ServiceTickets (
                    CreationDateTime
                    , Issue 
                    , ReportedDateTime
                    , ResolutionDateTime
                    , CreatedBy
                    , ServiceRequestNumber
                    , [Status]
                    , IsDeleted
                    , LocationId
                    , SubLocationId
                    , RequestorId
                    , ConfirmerId
                    , DepartmentId
                    )
                VALUES (
                    @CreationDateTime
                    , @Issue
                    , @ReportedDateTime
                    , @ResolutinoDateTime
                    , @CreatedBy
                    , @ServiceRequestNumber
                    , @Status
                    , @IsDeleted
                    , @LocationId
                    , @SubLocationId
                    , @RequestorId
                    , @ConfirmedBy
                    , @DepartmentId
                );
        -- updating the service ticket table
        ELSE
            UPDATE      ServiceTickets
                SET         CreationDateTime        = @CreationDateTime
                            , Issue                 = @Issue
                            , ReportedDateTime      = @ReportedDateTime
                            , ResolutionDateTime    = @ResolutinoDateTime
                            , CreatedBy             = @CreatedBy
                            , ServiceRequestNumber  = @ServiceRequestNumber
                            , [Status]              = @Status
                            , IsDeleted             = @IsDeleted
                            , LocationId            = @LocationId
                            , SubLocationId         = @SubLocationId
                            , RequestorId           = @RequestorId
                            , ConfirmerId           = ConfirmerId
                            , DepartmentId          = DepartmentId
                WHERE Id = @Id;

If you do need a transaction to do a proper Upsert, use this template, with HOLDLOCK, UPDLOCK hints. You do not need a BEGIN CATCH as XACT_ABORT ON will handle the rollback

CodePudding user response：

Use this pattern:

Begin Tran <TransactionName>

BEGIN TRY
   --Some Statements
   Commit Tran <TransactionName>
END TRY
BEGIN CATCH
    DECLARE @Error NVARCHAR(4000)
    SET @Error = ERROR_MESSAGE()
    ROLLBACK TRAN <TransactionName>
    RAISERROR (@Error, 16, 1)
END CATCH