Error Handling – Part 2

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 TRY/CATCH constructs to detect and handle errors. I consider this new school (modern) error handling since it was introduced with SQL Server 2005.

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.

I will be calculating the Fibonacci sequence using the sample program below. It uses many of the control of flow statements discussed previously. The program will have an arithmetic overflow error at iteration 94.

If you do not have a MATH database, create it before running the sample program. The first 93 numbers are stored in the table in the MATH database. The result window output of this program is shown below.

In short, just wrap any code you want to detect and handle errors with the TRY (BEGIN/END) pair of commands. This will cause the program to branch to a the CATCH (BEGIN/END) statement block when an error is raised. The rest is up to you on how you want to handle the error.

The below system functions can be called to obtain more information on the detected error.

ERROR_NUMBER() – the numerical error number.
ERROR_SEVERITY() – the severity of the error.
ERROR_STATE() – actual part of database engine that raised the error.
ERROR_PROCEDURE() – the name of procedure or trigger that caused the error.
ERROR_LINE() – the line number of offending statement.
ERROR_MESSAGE() – the detailed textual explanation.

A quick run-down of this talk is the TRY/CATCH construct is a-lot simpler and more powerful than its predecessor, the @@ERROR function. Consider using it in the future.

Next time I will be talking about how you can THROW or RAISE an user defined error.

Related posts

Leave a Comment