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!

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.

Quote:

---

Originally Posted by skhanal

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?

If you are using WHILE loop you can use BREAK.