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.