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.
Your stored procedure implementation here
If @@Trancount > 0 Rollback Transaction