Over on Experts Exchange, someone posted a question I felt I could answer. The user posted a question on how to return a failure and not run a job. I originally took the approach of putting a query at the top of the queries run by the job that would query the table of failure dates, and if today wasn’t in that table, it would run the step. The problem with my approach is it didn’t fit with the way the user requested it work.
Another contributor offered a suggestion to use the RAISERROR function. This is a great function that if you haven’t used yet, you just might think of some by the time you reach the end of this article.
Let’s look at a simple use of the RAISERROR.
IF ( SELECT SUM(errors) FROM errorTable) > 0 RAISERROR ( 'You have too many errors.' -- Message text. , 16 -- Severity. , 1 -- State )In this case we have table, errorTable, that we add records to as we discover errors. Later we run a query that counts the number of errors that have been entered into our errorTable. In this case, if the number of errors is greater than 0, we need to stop what we’re doing and report back to the client, there were too many errors found.
The IF statement should be straightforward, if not, please check out the archives for articles covering IF .. THEN statements in SQL. What is new is the RAISERROR. The basic three parameters this function takes are message text, severity, and state.
The message text is simple, this is just a string that will be returned to the user if this point in the code is reached. The severity is a little more difficult to explain. Severity can be any small int between 0 and 25. 20 through 25 are considered fatal, and you need to understand how your SQL server is going to respond to those before throwing an error with that severity level.
If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.
That leaves us a range of 0 – 19 that we can use. I generally use 11 when I’m trying to stop a job or a DTS package. You will have to experiment with this to learn what severity level you want to use and when. I try to use the minimum severity that responds appropriately to the business need of the error.
Finally, we have state. This is another smallint between 0 and 255. This is an identifier you get to choose, to help you locate where in your code the error was found. I usually increment from 0 to however many states I need in order to uniquely identify each break point in my code. I suggest you do the same.
As always, learning the RAISERROR command is easy. Mastering it’s use will take more time. I’m sure every one of you have built a stored procedure or some segment of code, that you would like to detect for an error case, and if found, stop processing at that point, right? This is the perfect solution for that case. I suggest plenty of testing in your development environment before rolling out a new RAISERROR… if only to be sure of how your code will respond!
If you have any questions on this command or any other, send them in! I’m always here to help answer any questions you may have about SQL.
References