How to set a save-point within a Transaction

   

      You can roll back portions of your transactions by creating save-points. Save-points are very useful in situations where errors are unlikely to occur. If you have a bunch of transactions in a stored procedure and one of these transactions fails for some reason, finding the fail one and rolling back all transactions can be an expensive operation specially if you can live with the thrown error.

SAVE {TRAN | TRANSACTION} { save-point name | @save-point var}

• save-point name is the name of the save-point you like to use. It is case-sensitive.
• @save-point variable is the name of a user defined variable which contains the save-point name.

     After you start a transaction, you can create a save-point for the transaction to rollback to the save-point location if part of the transaction is conditionally cancelled. To rollback to the save-point transaction, you can use ROLLBACK TRANSACTION save-point name command.

Let's look at an example to understand what's happening here. I have a table named Products and I want to update the status of an item in this table.

     I need to disable this item by updating its Active value to FALSE. InStock column controls if this item is available in the web application and I need to update this value to FALSE. Just for demo purposes, I need to do this in two update statements, if the first update statement fails, I still want to  continue and update the InStock column value to FALSE so customers can not order it anymore.

     I saved my first transaction as BeforeUpd then I tried to Update the item and raised an error for the transaction. In this point I need to continue with the next transaction according my requirements so I Rolled back my BeforeUpd Transaction and continue. Here is the results of example.