SQL: Try/Catch block with rollback

In .Net we are familiar with Try/Catch blocks and you can do this in SQL Server running SQL transactions as well with the additional benefit of “undoing” what was started if something were to fail mid-execution.
 
 
If there is ever a situation where you need the entire script to execute successfully and if there is a failure of any sort be able to revert/rollback the changes the script made, here is how you can structure your SQL:
 
Begin TRY
    BEGIN TRANSACTION
        -- Do your work here
    Commit TRANSACTION
End TRY

Begin CATCH
    IF (@@TRANCOUNT > 0)
      ROLLBACK TRANSACTION;
End CATCH

Related Posts

%d bloggers like this: