Sql Server TRY..CATCH

The other day had to do a giant update script on Microsoft SQL Server (this works in 2005+). And was wondering if something happens in the middle, what would happen to the transaction wrapping the sea of inserts/updates/deletes. Would all of them get rolled back, or only some?

It turns out that if you naively (although it makes sense) try to do:

BEGIN TRANSACTION
insert(1)...
insert(2)... -- Error here!
insert(3)...
COMMIT TRANSACTION

contrary to intuition, although insert(2) fails, and you would think the transaction gets aborted at this point, insert(3) still goes through. in order to rollback inserts 1 through 3, you would typically need to check for @@ERROR after each of the inserts, and do some sort of GOTO, which will let you jump to some error handling code section, which will perform ROLLBACK TRANSACTION.

Obviously this gets to be pretty, if not excruciatingly painful, to insert the check for @@ERROR after every single statement!

Thankfully there is a much cleaner way of doing this: TRY..CATCH

BEGIN TRANSACTION
BEGIN TRY
     insert(1)...
     insert(2)... -- Error here!
     insert(3)... -- This never happens.
     insert(4)...
     etc...
     -- If we got here there were no errors above.
     print 'Committing transaction...'
     COMMIT TRANSACTION
END TRY
BEGIN CATCH
     -- On insert(2) we drop through here. Nice!
     print 'Rolling back transaction...'
    ROLLBACK TRANSACTION
END CATCH;

Hope you find this useful... sure better than copy/pasting IF @@ERROR <> 0 a hundred times!