Comment on page
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:
- 1.Trap the error.
- 2.Perform alternative task.
- 3.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.
Trapping Divide By Zero
In [ ]:
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
In [ ]:
USE demo
DECLARE @UserInput INT
BEGIN TRY
SET @UserInput = 'Wakefield'
END TRY
BEGIN CATCH
PRINT 'Please input an integer value.'
END CATCH
Last modified 2yr ago