Microsoft SQL Server: ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Posted: August 23, 2011 in MS SQL Server
Tags:

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
Comments
  1. Pranav Rajyaguru says:

    Good KB article, Hemang…

    Keep posted….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s