Automatically rollback a transaction using TSQL if it can not be committed for any error…

If you need to commit a transaction only if it is completely successful (or otherwise it should roll back automatically) from a code (stored procedure), then using SQL Server’s try catch block will be best option.

Follow below syntax (pseudo code) to implement this functionality:

BEGIN TRY --Start the Try Block
BEGIN TRANSACTION --Start the transaction..
...
[PUT your code here]
...
COMMIT TRAN --Commit if everyting is success
END TRY
BEGIN CATCH -- To catch if there is any error in Try block
IF @@TRANCOUNT > 0 -- If transcation could not be commited
ROLLBACK TRAN
RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1) -- Raising error message and severity
END CATCH


Please note that this will not work prior to SQL Server 2005.

Comments

Popular Posts