Multiple Roll back for single Transcation

Question

I have to update multiple number of rows in table. My requirement is, If for any reason , the update result returns 0, then the entire transaction should be rolled back. At the same time if there is any exception occurred, then also the complete transaction must be rolled back.

In short I need to roll back the entire update transaction either if update statement returns 0 or if any exception has been occurred while updating the table.

This is the code I used.

CREATE TYPE [DBO].[EMPLOYEETYPETABLETYPE] AS TABLE
( EmployeeStatusKey INT, EmployeeStatusName VARCHAR(50) )

CREATE PROCEDURE [dbo].[usp_UpdateEmployeeStatusType]

@EmploymentStatusDetails [DBO].[EMPLOYEETYPETABLETYPE] READONLY 

AS
 BEGIN

SET NOCOUNT ON;

DECLARE @TransactionName varchar(20) = 'UpdateEmployeeStatus';

DECLARE @rowcount1 INT

    BEGIN

    BEGIN TRY
        BEGIN TRANSACTION @TransactionName

            UPDATE  ES1
            SET
                ES1.EmployeeStatusName=ES2.EmployeeStatusName

            FROM 
                 [dbo].[EmployeeStatusTypes] ES1
            INNER JOIN 
                @EmploymentStatusDetails ES2
            ON 
                ES1.EmployeeStatusKey= ES2.EmployeeStatusKey

            SET
                @ROWCOUNT1=@@ROWCOUNT

            IF @rowcount1 =0
                GOTO PROBLEM

        PROBLEM:
        ROLLBACK TRAN  @TransactionName
    COMMIT
END TRY
    BEGIN CATCH
        SET @ROWCOUNT1=0
        ROLLBACK TRAN @TransactionName
    END CATCH

    IF @rowcount1 =0
        SELECT  -178,@rowcount1;
    ELSE
        SELECT  178,@rowcount1;
    END 


 END

I am passing a datatable to the stored procedure from the C# code. When I execute the Sp, No error is thrown But When I call it from the C# code I am getting the Exception

Exception: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Please help and Thanks in advance....

I also suggest you to print @@trancount in multiple places to see why you get into this issue. — FLICKER, Mar 17 '16 at 21:33

score 0 · Answer 1 · answered Mar 09 '16 at 10:39

0

Remove that awful GOTO

IF @rowcount1 =0
  ROLLBACK TRAN  @TransactionName
ELSE
  COMMIT TRAN @TransactionName

And review TRY-CATCH documentation. You have to check whether there is any transaction to commit or rollback by checking XACT_STATE() value.

answered Mar 09 '16 at 10:39

Ivan Starostin

8,798
5
21
39

If I remove the goto, then what if the update statement returns 0 (Say the condition when primary key =0) – Bimzee Mar 10 '16 at 04:53
Well I provided some code for you starting with `if`. Right now your code will fail on unconditional COMMIT right after ROLLBACK. – Ivan Starostin Mar 10 '16 at 07:28

FLICKER · Answer 2 · 2016-03-18T17:19:50.067

I tested your script with simplified one:

BEGIN TRY
    print 'A:' + cast(@@trancount as varchar)
    BEGIN TRANSACTION tranName

    print 'B:' + cast(@@trancount as varchar)
    GOTO PROBLEM -- you can comment this to simulate there is no error

    PROBLEM:
    begin
      ROLLBACK TRAN  tranName
      print 'there is a problem'
    end
    print 'C:' + cast(@@trancount as varchar)
    COMMIT
END TRY
BEGIN CATCH
    print 'D:' + cast(@@trancount as varchar)
    ROLLBACK TRAN tranName
END CATCH

when there is a problem, output is like

A:0
B:1
there is a problem
C:0
D:0
Msg 3903, Level 16, State 1, Line 18
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

if I comment the GOTO PROBLEM so there is not a problem, output is like this:

A:0
B:1
there is a problem
C:0
D:0
Msg 3903, Level 16, State 1, Line 18
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

you see that the section under GOTO, still is executing.

finally, the COMMIT still happens where there is not transaction available, so COMMIT throws and error. This means your script throws an exception.

If you get rid of the GOTO, you'll be good. using GOTO is a bad practice anyway.

Multiple Roll back for single Transcation

2 Answers2