SQL Server Exception Handling - Basketball Analogy

SQL Server 2005 has introduced a really cool way of handling exceptions through a TRY ... CATCH construct that should be familiar to most developers in this day and age. In my opinion this waslong overdue as it really was a pain to handle errors using previous versions of SQL Server.

Since I'm a firm believer in the "No pain, no gain" philosophy, let's remind ourselves how painfulthe old syntax really was using a sports analogy.

Old Way

So let's take a hypothetical situation and pretend that I am American Billionaire, Mark Cuban, owner of the Dallas Mavericks. I've decided to remove Dirk from the lineup because I recently found out that he is abig fan of the 'Hoff (whom Mark Cuban hates for obvious reasons). So he's influenced Rick Carlisle to remove Dirk from the lineup to insert Brandon Bass. Let's see what that code would like.

begin trans
delete from lineup where playerID = 41
if @@error <> 0
goto err1
insert into lineup values (32, 'Brandon Bass')
if @@error <> 0
goto err2
commit trans

err1: print 'Error occured removing player from lineup'
goto eend

err2: print 'Error occured adding player to the lineup'

:eend

So we have to check whether or not there was an error after each statement. Perhaps, Dirk wouldn't come off the floor. Or perhaps, Brandon Bass tripped on his shoelaces, fell over the gatorade and broke his ankle while checking into the game. And if we wanted customized error messages for each scenario, wewould have to have multiple labels to handle them (Rather unrealistic but it proves the point).

New Way

So now let's see a more elegant solution with the new TRY ... CATCH construct.

BEGIN TRY
insert into lineup values (32, 'Brandon Bass')
END TRY

BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO

As you can see it is much more elegant than the old method and improves readability and robustness of the code. Anytime an error is encountered withinthe TRY block, control is directed to the CATCH block, where you could display or log the corresponding error. So I hope that anyone using SQL 2005+ will start to use this syntax if you aren't already.