Chapter 23. T-SQL Error Handling


  • Legacy error handling

  • Try/catch blocks

  • Rethrowing errors

So an atom goes into a bar and says to the barkeeper, "Hey, I think I've lost an electron."

"Are you sure?" asks the barkeep.

"Of course, in fact, I'm positive."

Lame, I know, but it's my favorite geek joke; I couldn't help it. Back to SQL, despite our best efforts, any application can lose an electron every once in a while—the trick is to handle it in a positive way.

Of course, all robust programming languages provide some method for trapping, logging, and handling errors. In this area, T-SQL has a sad history (almost as sad as that joke), but it's made significant progress with SQL Server 2005.

There are two distinctly different ways to code error handling with SQL Server:

  • Legacy error handling is how it's been done since the beginning of SQL Server, using @@error to see the error status of the previous SQL statement.

  • Try/catch was introduced in SQL Server 2008, bringing SQL Server into the 21st century.

Legacy Error Handling

Historically, T-SQL error handling has been tedious at best. I'd prefer to not even include this legacy method of handling errors, but I'm sure you'll see it in old code, so it must be covered.

Get Microsoft® SQL Server® 2008 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.