Tran, roll back question

Question

Tran, roll back question

adonetok

SSCrazy Eights

Points: 8395
More actions
May 3, 2017 at 8:10 am

#342751

I created a store procedure to applied transaction and roll back.
But, roll back never work as expected.
How to fix it?
BEGIN TRAN
--- some codes to insert data into multiple tables

  SET @myerror = @@ERROR
  IF @myerror <> 0
   BEGIN
    GOTO ERROR_HANDLER
   END
ERROR_HANDLER:
  IF @myerror <> 0
   BEGIN
    ROLLBACK TRAN // never work
   END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RandomEvent SSCrazy Points: 2963 More actions · Answer 1

adonetek don't know if you can change the code but this is exactly why they brought in the try\catch block to allow this kind of operation. Looking at your code I would say that the call to GOTO ERROR_HANDLER has put the rollback outside of the current transaction so there is nothing there to rollback.

Matt Simmons SSCrazy Points: 2846 More actions · Answer 2

You should be using a Try Catch Block:


BEGIN TRY
    BEGIN TRANSACTION;
    --SQL Goes Here

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(),
            @ErrorSeverity INT = ERROR_SEVERITY(),
            @ErrorState INT = ERROR_STATE();

    PRINT '';
    PRINT @ErrorMessage;

    ROLLBACK TRANSACTION;
END CATCH

Regards,
Matt

adonetok SSCrazy Eights Points: 8395 More actions · Answer 3

Thank you Matt.
One more question.
I want this store procedure to return a value to application.
I added a parameter
@final int output
If there is no error:
set @final=0
if there an error
set @final=1
How to add @final in try and catch block?

Matt Simmons SSCrazy Points: 2846 More actions · Answer 4

I would at it after the Commit and Rollback statements, respectively.

Regards,
Matt