Let’s get back into SQL 200 by learning to deal with errors. Churchill had a great quote that applies to development (database or not) “He who fails to plan is planning to fail.” No matter how well you think you’ve planned your code, it’s going tofail.

 

The question is, will it fail gracefully, or will it EPIC FAIL?

What information can you get on errors?

Whenever you run encounter an error within your T-SQL, you’ll be able to access the
following pieces of information

• Error Number — every error message in SQL server has to have a unique number to
  idenitify it. Out of the box, they’re all defined. If you want to define custom
  errors, you’ll have to pick a number that’s not in use (a number greater than 50,000).
• Error Message — The message should be something more meaningful than “An error has
  occurred.” But sometimes they are just that. Google will be your best friend until you
  get a good idea of what the error messages actually mean. Keep in mind how useless some
  error messages can be when you start defining your own errors. Give the user some
  useful information, that way you can solve the problem more quickly in the future.
• Severity — how bad is it? These severities tell how bad the problem is, You’ll see
  values from 0 – 24. The higher the number, the more severe the error. Severities below
  10 are informational. Errors between 11 and 16 are errors I see most often. Mostly
  because these are the ones I use when raising errors within stored procedures and jobs.
  Severities over 16 require your adminstrator to correct them.
• Procedure Name — If your error occurs within a stored procedure, function, or
  trigger, the error will tell you which parent object the error occured in. So if you
  have a stored procedure that calls a function, which in turn is called from an ad-hoc
  query, the error raised will tell you if the error was in the function, the stored
  procedure, or the ad-hoc query. Very useful when you’re trying to track down the
  problem!
• Line Number — Once you know where the error is, for example let’s say the error
  raised says the error is in a stored procedure, the line number will tell you what line
  within that stored procedure errored. I’d like to give you a warning, the line number
  it gives isn’t always the exact line the error occured on, but it will help you figure
  out where to begin your search. I’ve found white space can throw off the line number,
  as well as IF..THEN statements can affect what line number is returned.
• State — The state is where in the SQL code the error is generated. This points
  more to where SQL errored, and less where your T-SQL code errored. Unless you’re doing
  some pretty deep error handling, I doubt you’ll need this piece of information on your
  errors.

How do you get information on your errors programatically?

Now that you know what all information you can get on your errors, you need to know how
to get at that information. Well, you can use the following stored procedures to get at
the information:

• ERROR_NUMBER() or @@ERROR
• ERROR_MESSAGE()
• ERROR_SEVERITY()
• ERROR_PROCEDURE()
• ERROR_LINE()
• ERROR_STATE()

Whenever you want to grab the different pieces of information on the error that just
occured, use one of these stored procedures (or grab the value of @@ERROR). But there’s
a trick: you have in order to get data out of these stored procedures, you have to be
inside a TRY..CATCH
block. Check out my previous article, for more info on how to set up and use a
TRY..CATCH block.

…and finally

The last topic you’ll need to learn in order to deal with errors is how to raise your
own errors. I’ve covered this in a two articles, and I’m
going to do one more article on RAISERROR to share a larger picture of what all you can
do with your own errors.
But the moral of the story is you can define what an error is. That’s helpful when you
want to build in business logic into your stored procedures. Maybe you want to define a
positive integers only error message. Maybe you need to define what a valid account
number is. With RAISERROR you can build and handle those errors.

If you have any questions on errors, or SQL in general…let me know. I’m here to help!