Lesson 33. Error Processing

Error processing is important in any programming language. Weird things will happen, and your code has to be ready to handle weirdness. Sometimes you know what weirdness to expect like a variable value being NULL, and you have to program what to do in that instance. Other times, the user will do something unexpected and you have to write a general error handler.

In any case, the point of error processing is to keep the program going when an error happens. The general process is:

Trap the error.

Perform alternative task.

Report the error.

T-SQL has a way to trap errors with the standard TRY CATCH approach that you will find in most major programming languages. The general syntax of a TRY CATCH block is:

BEGIN TRY
--Process that may create an error
END TRY
BEGIN CATCH
--Process to handle error
END CATCH

T-SQL has a handful of functions that help with error reporting.

Function Name	Function Definition
ERROR_LINE	The line number the error occurred on.
ERROR_MESSAGE	Plain language description of the error.
ERROR_NUMBER	The number of the error.
ERROR_PROCEDURE	The name of the function or stored procedure that produced the error.
ERROR_SEVERITY	The severity value of the error.
ERROR_STATE	The state number of the error.

The error number, state, and severity are particularly useful. There is a long list of database engine errors that correspond to a number. You can check for that value in an equality when building your error handling logic.

It is rare when I do error processing. When I build data warehouse load processes, each stored procedure I write performs a single task. An example would be a stored procedure that does nothing but load a single dimension table. The entire load process is engineered so that an error brings the entire thing to a halt. I do this to prevent train wrecks. Load processing is a series of events that have to happen in order. When a load processes in any other way than intended, then the train wreck metaphor becomes readily apparent.

Since my processes run in SQL Server Agent, any errors are logged without me having to write code. However, from time to time, I do have to write errors. Recently, I had to write code that was going to be leveraged by an application written in .NET. In this case, reporting errors was necessary so they could be reported to the user.

Examples

Trapping Divide By Zero

USE demo

BEGIN TRY
Print 1/0
END TRY
BEGIN CATCH
PRINT 'Error '+CAST(ERROR_NUMBER()AS NVARCHAR(6))+' '+ERROR_MESSAGE()
END CATCH

Creating A Custom Error Message

USE demo

DECLARE @UserInput INT

BEGIN TRY
SET @UserInput = 'Wakefield'
END TRY
BEGIN CATCH
PRINT 'Please input an integer value.'
END CATCH