Handling Errors in Stored Procedures

By Garth Wells on 5 February 2001 | Tags: Stored Procedures

The following article introduces the basics of handling errors in stored procedures. If you are not familiar with the difference between fatal and non-fatal errors, the system function @@ERROR, or how to add a custom error with the system stored procedure sp_addmessage, you should find it interesting.

The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do (assuming it’s a non-fatal error) is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. Notice that the previous sentence is specific to non-fatal errors. There are two type of errors in SQL Server: fatal and non-fatal. Fatal errors cause a procedure to abort processing and terminate the connection with the client application. Non-fatal errors do not abort processing a procedure or affect the connection with the client application. When a non-fatal error occurs within a procedure, processing continues on the line of code that follows the one that caused the error.

The following example demonstrates how a fatal error affects a procedure.

USE tempdb
go
CREATE PROCEDURE ps_FatalError_SELECT
AS
SELECT * FROM NonExistentTable
PRINT 'Fatal Error'
go
EXEC ps_FatalError _SELECT

--Results--

Server:Msg 208,Level 16,State 1,Procedure ps_FatalError_SELECT,Line 3
Invalid object name 'NonExistentTable'.

The SELECT in the procedure references a table that does not exist, which produces a fatal error. The procedure aborts processing immediately after the error and the PRINT statement is not executed.

To demonstrate how a non-fatal error is processed, I need to create the following table.

USE tempdb
go
CREATE TABLE NonFatal
(
Column1 int IDENTITY,
Column2 int NOT NULL
)

This example uses a procedure to INSERT a row into NonFatal, but does not include a value for Column2 (defined as NOT NULL).

USE tempdb
go
CREATE PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
INSERT NonFatal VALUES (@Column2)
PRINT 'NonFatal'
go
EXEC ps_NonFatal_INSERT

--Results--

Server:Msg 515,Level 16,State 2,Procedure ps_NonFatal_INSERT,Line 4
Cannot insert the value NULL into column 'Column2',table 'tempdb.dbo.NonFatal'; 
column does not_allow nulls.INSERT fails.  
The statement has been terminated.
NonFatal

The last line of the results (shown in blue) demonstrates that the error did not affect the processing of the procedure—the PRINT statement executed.

You might be wondering what actions cause fatal errors. Unfortunately, the actions that cause a fatal error are not well documented. Each error has an associated severity level that is a value between 0–25. The errors with a severity level of 20 or above are all fatal, but once you get below this value there is no well-defined rule as to which errors are fatal. In truth, though, worrying about which errors are fatal is a bit useless because there is no code you can implement that will allow you to handle them gracefully. Of course, you can use pro-actice coding to make sure fatal-errors do not occur. For example, if your application allows users to type in the name of the table on which a query is based you can verify it’s existence before referencing it with dynamic SQL.

@@ERROR

The @@ERROR system function is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of the function immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset when the next statement executes successfully.

Let’s alter ps_NonFatal_INSERT to use @@ERROR with the following.

USE tempdb
go
ALTER PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
INSERT NonFatal VALUES (@Column2)
IF @@ERROR <>0
 BEGIN
  PRINT 'Error Occured'
 END

--Results--

The command(s)completed successfully.

When an error occurs, the PRINT statement produces the "Error Occurred" message. The following code shows the results of a valid call to ps_NonFatal_INSERT.

USE tempdb
go
EXEC ps_NonFatal_INSERT 111

--Results--

(1 row(s)affected)

The next example shows the results of a call that produces the "does not allow nulls" error.

USE tempdb
go
EXEC ps_NonFatal_INSERT

--Results--

Server:Msg 515,Level 16,State 2,Procedure ps_NonFatal_INSERT,Line 4
Cannot insert the value NULL into column 'Column2',table 'tempdb.dbo.NonFatal'; 
column does not_allow nulls.INSERT fails. 
The statement has been terminated.
Error Occured

The last line of the results (in blue) indicates the PRINT statement executed as expected.

RAISERROR

The RAISERROR statement is used to produce an ad hoc error message or to retrieve a custom message that is stored in the sysmessages table. You can use this statement with the error handling code presented in the previous section to implement custom error messages in your applications. The syntax of the statement is shown here.

RAISERROR ({msg_id |msg_str }{,severity ,state }
   [ ,argument [ ,,...n ] ] ))
   [ WITH option [ ,,...n ] ]

A description of the components of the statement follows.

msg_id

The ID for an error message, which is stored in the error column in sysmessages. The domain of the error column for custom messages are values greater than 50,000.

msg_str

A custom message that is not contained in sysmessages. The maximum length of the message is 400 characters. Variable substitution can be used to create a more meaningful message.

severity

The severity level associated with the error. The valid values are 0–25. Severity levels 0–18 can be used by any user, but 19–25 are only available to members of the fixed-server role sysadmin. When levels 19–25 are used, the WITH LOG option is required.

state

A value that indicates the invocation state of the error. The valid values are 0–127. This value is not used by SQL Server.

Argument, . . .

One or more variables that are used to customize the message. For example, you could pass the current process ID (@@SPID) so it could be displayed in the message.

WITH option, . . .

The three values that can be used with this optional argument are described here.

LOG - Forces the error to logged in the SQL Server error log and the NT application log.

NOWAIT - Sends the message immediately to the client.

SETERROR - Sets @@ERROR to the unique ID for the message or 50,000.

The number of options available for the statement make it seem complicated, but it is actually easy to use. The following shows how to create an ad hoc message with a severity of 10 and a state of 1.

RAISERROR ('An error occured updating the NonFatal table',10,1)

--Results--

An error occured updating the NonFatal table

The statement does not have to be used in conjunction with any other code, but for our purposes it will be used with the error handling code presented earlier. The following alters the ps_NonFatal_INSERT procedure to use RAISERROR.

USE tempdb
go
ALTER PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
DECLARE @ErrorMsgID int

INSERT NonFatal VALUES (@Column2)
SET @ErrorMsgID =@@ERROR
IF @ErrorMsgID <>0
 BEGIN
  RAISERROR ('An error occured updating the NonFatal table',10,1)
 END

When an error-producing call is made to the procedure, the custom message is passed to the client. The following shows the output generated by Query Analyzer.

USE tempdb
go
EXEC ps_NonFatal_INSERT

--Results--

Server:Msg 515,Level 16,State 2,Procedure ps_NonFatal_INSERT,Line 6
Cannot insert the value NULL into column 'Column2',table 'tempdb.dbo.NonFatal'; 
column does not_allow nulls.INSERT fails.
The statement has been terminated.
An error occured updating the NonFatal table

The output may seem confusing because we still see the same error message displayed before we started using RAISERROR. The custom error (in blue) is also displayed. The output is a function of Query Analyzer and we cannot control its behavior. When you develop client applications you will have control over what is displayed to the end user so the output will be less confusing.

Adding a Permanent Custom Message

If you have a message that is going to be used frequently, it is more efficient to add it to the sysmessages table and reference it by its unique ID. The system stored procedure sp_addmessages adds an error message to sysmessages. The following shows how to add a new error message.

sp_addmessage @msgnum =50001,
@severity =10,
@msgtext ='An error occured updating the NonFatal table'

--Results--

(1 row(s)affected)

Note that the ID for a custom message must be greater than 50,000. The new message can be accessed with RAISERROR using the following.

RAISERROR (50001,10,1)

--Results--

An error occured updating the NonFatal table

In a forthcoming article I will show you how to access a custom error using the Errors collection of the ADO Connection Object. This approach allows you to control the type of error messages presented to your end-users.

Garth
www.SQLBook.com