Posts Tagged ‘ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION’

I have noticed that If we are using a transaction within any Stored Procedure like


CREATE PROCEDURE ....
AS
BEGIN
BEGIN TRANSACTION
...
IF @@Error <> 0 GOTO Rollback_Transaction
...
IF @@Error <> 0 GOTO Rollback_Transaction
...
IF @@Error <> 0 GOTO Rollback_Transaction
COMMIT TRANSACTION
PRINT 'Success'
Rollback_Transaction:
ROLLBACK TRANSACTION
PRINT 'Fail'
END

Error encountered when I execute this code block. This is because while executing the code flow when “COMMIT TRANSACTION” is reached, execution goes past “Rollback_Transaction:” label and execute the “ROLLBACK TRANSACTION” and as “COMMIT TRANSACTION” is executed there is no OPEN transaction to rollback.

Instead you can use like

COMMIT TRANSACTION
PRINT ‘Success’
GOTO After_Success:
Rollback_Transaction:
ROLLBACK TRANSACTION
After_Success:
PRINT ‘Fail’

Here you can add one more label like “After_Success” that will execute the flow “After_Success” label when Commit Transaction is reached.

Advertisements