Professional SQL Server™ 2005 Programming

11.6. Dealing with Errors

Sure, you don't need this section. I mean, your code never has errors, and you never run into problems, right? OK, well, now that you've had your moment of fantasy for today, get down to reality. Things go wrong. It's just the way that life works in the wonderful world of software engineering. Fortunately, you can do something about it. Unfortunately, you're often not going to be happy with the tools you have — SQL Server now has much improved error handling in the form of TRY/CATCH, but it still isn't quite everything you might be accustomed to from procedural languages. Fortunately again, there are ways to make the most out of what you have, and ways to hide many of the inadequacies of error handling in the SQL world.

Four common types of errors can happen in SQL Server:

• Errors that createruntime errors and stop your code from proceeding further.

• Errors that informational in nature and do not create runtime errors. A non-zero error number is returned (if you ask), but no error is raised (and so no error trapping will be activated unless you are testing for that specific error).

• Errors that create runtime errors but continue execution within SQL Server such that you can trap them and respond in the manner of your choosing.

• Errors that are more logical in nature and to which SQL Server is essentially oblivious.

Now, here things get a bit sticky, and versions become important, so hang with me as I lead you down a winding road.

Error handling in the SQL Server ...