2012-06-01

How to rollback a SQL transaction if it fails at some point

Sometimes we write SQL stored procedures with more than one SQL statements within it. For example, let’s say we have a stored procedure which do both an insert and update operation. In order to perceive the consistent of data, we need both statements to be successful. Otherwise, we don’t need to perform both if at least one of them fails. So we need to rollback the whole transaction if any of the statement fails. Following is what you can do.

Place the Stored Procedure inside a Try block. And at catch block, roll back the transaction.

BEGIN TRY
        Your stored procedure implementation here
END TRY

BEGIN CATCH
        If @@Trancount > 0 Rollback Transaction
END CATCH

No comments: