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!