Exception Handling

Introduction

Exception handling is a method of catching errors in code and working with them before displaying a message to the user (if desired).

Normally, we refer to a syntax problem in code as an "error". The IDE normally alerts us that there is a problem (using red sqiggly lines, for example), so that we can take care of the issue. The program or code will not usually execute if there is an error.

An exception is something that happens when the code executes. If nothing is done to handle the error, the program often crashes and the user may receive a serious and sometimes confusing message. To handle an exception when it happens, rogramming languages use what is called a TRY-CATCH block. This wraps the code in special statements that "catch" or "trap" the error so it can be examined and dealt with in a more practical manner.

In T-SQL, a try-catch block is coded like this:

BEGIN TRY ... main coding statements here END TRY BEGIN CATCH END CATCH

The way a try-catch block works is that it executes the code in the TRY section. If an exception occurs (we don't refer to thesse as errors, but exceptions), the execution of the TRY code is stopped immediately and the program jumps to the CATCH block.

Catching the Exception

What happens in the CATCH block?

There are two common steps that are coded in the CATCH block: 1)logging the error, and 2) passing it back to the calling program.

T-SQL has several built in functions that can be used to learn more about the current exception. The main ones are:

  • ERROR_SEVERITY() returns the severity.
  • ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_LINE() returns the line number inside the routine that caused the error.
  • ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

These functions can be used in the CATCH block to read the current error. For example,

select ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE()

will display the information to the Query Manager console.

It would be more common, especially when used in a stored procedure, for the exception information to be written to a log table that could be reviewed later. For example:

insert into my.exceptionlogs (procname, linenumber, message) values (ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE());

Lifetime of an Exception

One interesting thing about the exception in T-SQL is that it is stored in memory during the execution of the CATCH block. This means that even it the CATCH block executes another procedure, the exception information is also available to that procedure. This means that programmers can log exceptions using a standardized stored procedure, instead of duplicating the same code in all the procedures.

Passing along the Exception

Many times - even if the exception is logged in the CATCH block, the calling program or application may also need to be aware that an exception occurred. In those cases, the T-SQL code needs to pass the exception back to the calling program. This is called "throwing" the exception. The T-SQL statement is simple:

throw;

This passes the exception information back to the calling program, without changes.

It is also possible to change the exception by calling "throw" with the error number, message and linenumber, such as

THROW 51000, 'The employee record does not exist.', 1;

The first parameter, can be any number. SQL Server has a long list of error numbers that provide more information about the error. These can be retrieve for the current exception with ERROR_NUMBER(). But the error number can be any number when creating a new exception message for the THROW statement.

More information on the Exception Handling can be found here.