Results 1 to 4 of 4

Thread: Help needed on Rollback transaction

  1. #1
    Join Date
    Dec 2008
    Posts
    2

    Help needed on Rollback transaction

    I am trying to work on a sql script. In the stored procedure, there is a cursor. If a certain condition is true, the whole transactions that occurred in the cursor must rollback.

    On testing the stored procedure I have encountered:
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 3.


    In summary, the stored procedure goes like this:

    Alter Procedure [ProcedureName]
    As
    Begin

    Begin Transaction;
    Declare variables here

    Insert statements

    Declare cursorname
    If Condition = True
    SET @err_message = 'Error: Setup is missing.'
    RAISERROR (@err_message, 11,1)
    ROLLBACK TRANSACTION
    Else
    Insert into Table statement here

    CLOSE cursorname
    DEALLOCATE cursorname
    Fetch Next

    Appreciate it if someone could help me on this. Thanks in advance and a merry christmas!

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You need to exit out of cursor loop after rollback transaction. Because after first rollback, the insert statement after else is not part of a transaction anymore.

    Also if second row meets the condition rollback does not have corresponding begin tran and it fails with the error you saw.

  3. #3
    Join Date
    Dec 2008
    Posts
    2
    Quote Originally Posted by skhanal View Post
    You need to exit out of cursor loop after rollback transaction. Because after first rollback, the insert statement after else is not part of a transaction anymore.

    Also if second row meets the condition rollback does not have corresponding begin tran and it fails with the error you saw.
    Uh. How do I go about to exit the cursor? Do I need to close and deallocate the cursor after/before I exit?

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    If you are using WHILE loop you can use BREAK.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •