April 20, 2015 at 2:11 pm
My team is starting to implement error handling in our sprocs. One question we have is whether or not to use unique error numbers for custom errors (ie Errors we throw after doing some sort of validity check, not SQL Server errors). For example, we might check the value of a parameter and then throw an error that says "Parameter State_Date must be less than today, please retry".
We are using SQL Server 2012 and will be using the THROW statement, not RAISERROR, so we don't HAVE to put the numbers in sys.messages. Also, we are going to log the errors in a table, along with the error message, sproc name, line number, etc.
Is it useful to maintain a custom list of error numbers and messages? Or is it just as useful to use one standard error number and add a custom error message (which we can then search for in our code, or use the sproc name & line number we logged)? And if it is worth maintaining a list of numbers plus messages, should we go ahead and put them in sys.messages?
Thanks for any advice!
JB
April 20, 2015 at 9:31 pm
One thing you may want to think ahead to is what happens if you have a heavily hit proc that encounters an error due to some structure change that was not accounted for? Does this create a huge volume of noise in your log table and skew your growth projection in your database and cause other bad problems (disk I/O issues, timeouts, dogs and cats living in harmony)? Who will be monitoring this table and with what frequency? How long are you keeping these errors around for?
My thought to the original question this is that if you have a standard message along the lines of "Procedure [name] failed because of bad parameter values in [parameter name]" or "Procedure [name] failed at line [line number] due to divide by zero" these are perfect for a generic entry in the sys.messages table and calling them by way of this. You could then standardize on this error number being used in your app instead of every developer using their own number to mean something different. As with everything it sort of depends on what you are after and how much you are willing to enforce standards. If you are in a pretty mature enterprise environment it might be worth pushing towards sys.messages because then you can change the text at will without changing it in potentially hundreds or thousands of procs. If you are in an environment where your devs are wearing their cowboy coding hats with pride it may be a big fight that isn't worth taking on.
If I were in the position of trying to write these error messages some of the things that I would be thinking of is what is my target audience for reading this log. If the answer to that is an end user then I am going to log the information in a drastically different way than I would for a log intended for a developer.
I hope I have given you some things to think of and some helpful direction.
April 20, 2015 at 11:07 pm
You don't have to put messages in sys.messages to do what you want with RAISERROR.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply