Category: Error Handling

Erik Darling shares some tips:

Debugging, like error handling, is a design choice that is fairly easy to make at the outset of writing a stored procedure, and is usually a lot easier to get in there if you do it from the get-go.

The number of times I’ve had to go back and add debugging into something only to introduce debugging bugs is actually a bit tragic.

If you’ve never brought down a system with your monitoring process or introduced bugs via debugging code, you’re not living life to its fullest.

Erik Darling intimates that some of our code might occasionally have errors or might experience circumstances in which not everything is in perfect alignment:

Okay, look, the best post about this is, of course, by Erland Sommarskog: Error and Transaction Handling in SQL Server

Just like Erland, it has three parts and three appendices. If you want to learn how to do a whole lot of things right, give yourself a couple days to read through and digest all that.

What I’m here to talk about is some of the stuff you should think about before implementing any error handling at all.

I agree with most of Erik’s opinion here. My very mild disagreement is that I’ll still protect against things like invalid parameters or logic errors (start date before end date) in the stored procedure. I do that for three reasons:

• Defense in depth isn’t just a security principle–it’s also a code practices principle.
• The app gets things wrong, too. Sometimes, the app dev accidentally sends parameters in the wrong order, and it’s better to get an error early on in development versus thinking everything works because the procedure called successfully and ship it.
• Even if “the” app correctly handles inputs, there’s always a chance some other app or process will call this stored procedure and it might not have the same error handling code built in.