Pages

June 27, 2012

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!

Excel Substitute function

2 small things that make fairly regular data munging tasks a little easier:

Excel

When dealing with lots of adhoc data and relational databases, you want to be able to generate insert/update SQL statements in excel, based on some data in the spreadsheet.

Typically you will have some template SQL statement with placeholders for data that varies from row to row. Here is one approach to accomplishing this in Excel with relative ease.

Put your SQL template in cell A1. Lets say my template looks like this:

insert into some_table (one, two) values (@some_value, '@some_string_value')

The funny looking  @ denote placeholder where we want to insert our variable content.
This turns out to be fairly easy to do in Excel, if you use SUBSTITUTE() function.

=SUBSTITUTE(SUBSTITUTE($A$1, "@some_value", B1), "@some_string_value", B2)