Error Handling – Part 1

I am going to continue my articles on controlling the execution flow of statements in a Transaction SQL (T-SQL) program.

Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next.

The only exception to this rule is error handling. A runtime error may cause the program to branch to a error handler and exit the program.

Today, I want to talk about how to use the @@ERROR system function to determine if an error occurred. This function returns the last returned error code. It is important to save this value right away in a variable since the next successfully executed statement overwrites the previous value.

I consider this old school error handling since many older programs, including SQL Server Management Studio job scripting, combine the check with a GOTO statement to one central error handler.

There are two schools of thought when it comes to handling errors.

  1. Exit the program upon detecting the error. Make sure the appropriate error code and message are logged. An operator will have to manually correct the problem and re-run the program.
  2. Upon detecting the error, wait some period of time (ms). Then, retry the operation. Hopefully the pause in program execution has allowed the system issue, such as a lock, to clear. After a number of tries, handle the error like step 1 above.

Let’s come up with an example program that will have a run-time error. During my studies in college, I took many math courses. One number theory course went over the Fibonacci numbers.

This sequence can be defined as the following recurrence relationship.

F(0) = 0
F(1) = 1
F(N) = F(N-1) + F(N-2)

The first 13 Fibonacci numbers are listed below.

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144 …

The cartoon below shows how eating nachos using the Fibonacci sequence as a chip selection algorithm will result in a stomach ache!

The best part of this sequence is that it has exponential growth. As a result, we will have an arithmetic overflow when calculating the 94th fibonacci number. I am using a BIGINT as variables in the calculation. Using a integer variable of lesser precision will make the overflow happen quicker.

The Fibonacci sample program is below. It uses many of the control of flow statements discussed previously.

The first 93 numbers are stored in the table in the MATH database. If you do not have a MATH database, create it before running the program.

The error output of this program is shown below.

The first key point to take away from this talk is that the @@ERROR status needs to be saved and examined after any key action. If there is an error, you need to figure out how you want to handle it. The following white paper for SQL Server 2000 still has value for this technique.

The second key point to note is that certain server settings effect how errors are handled.

SET ARITHABORT – this should be set ON for optimal query plans. When set OFF, it will evaluate divide-by-zero and arithmetic overflow errors as NULL.

SET ANSI_WARNINGS – when this is set ON, divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated.

SET ARITHIGNORE – this setting determines if error messages are returned.

In a nutshell, you should determine how you want to handle errors. Either abort the current transaction or retry a number of times. The @@ERROR function is still in heavy use in the world. You might even stumble upon central error handling via the GOTO statement. I consider this the old school way of handling errors.

Next time, I will be talking about the TRY/CATCH construct. This new school way of handling errors was introduced in SQL Server 2005.

Related posts

Leave a Comment