-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|