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.