Microsoft® SQL Server® 2008 Bible

Chapter 23. T-SQL Error Handling

IN THIS CHAPTER

• 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.

What's New with Error Handling?

Unfortunately, there is nothing new in 2008 when it comes to error handling. However, when performing code reviews for third-party software vendors, I rarely see production code with any error handling, much less the improved try...catch, which ...