Data Management

Get a handle on errors in SQL Server stored procedures

Error handling in SQL Server stored procedures is not difficult, but it may be different from what you're used to. Pick up the basics and a general strategy in this article.


The idea of "error handling" in a SQL Server stored procedure is something of a misnomer—in most cases, your only responsibility is to return an error to the client, which the access provider usually can do on its own. But it's useful to know when an error occurs during the execution of T-SQL code and what variety of error it was. In this article, I'll introduce you to the basics of detecting and dealing with errors in your SQL Server stored procedures.

You're in error
SQL Server has somewhere in the neighborhood of 3,800 predefined error codes, which are maintained in the master catalog's sysmessages table. Each error code has a corresponding severity level, which serves as a rough indication of just how bad an error is. Severity levels range from zero to 25. Everything above 20 qualifies as a fatal error, which basically means that the offending stored procedure is immediately terminated when the error occurs, and any connection with the client must be reinitialized. Nonfatal errors simply prevent the offending line from executing, and the procedure will continue with the next line. All error codes also have a matching text description.

So, what can you do with this information now that you have it? Not much, I'm afraid, since the only information you can access in a procedure when an error occurs is the error number, obtainable through the @@ERROR system function. However, you can look up the actual error message and severity in the sysmessages table—unless, of course, your code just experienced a fatal error.

Error-handling basics
Let's assume you have a table called NoNullsAllowed with two fields, Field1 and Field2. Both fields, as you'd expect, were created with the NOT NULL flag. Now let's further assume you create the following procedure, which updates the NoNullsAllowed table:
CREATE PROCEDURE IllegalInsert
AS
INSERT NoNullsAllowed VALUES (NULL, NULL)
Print 'Error occurred'
Print @@ERROR

Assume that you also have the following procedure:
CREATE PROCEDURE SampleProcedure
AS
EXEC IllegalInsert
Print @@ERROR

What would you expect the output to be if you invoked SampleProcedure from the Query Analyzer? This is kind of a trick question, so check the following carefully:
Server: Msg 515, Level 16, State 2, Procedure IllegalInsert, Line 2
Cannot insert the value NULL into column 'Field2', table
'tempdb.dbo.NoNullsAllowed'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Error occurred
0
0

This little exercise should tell you four things about error handling in T-SQL:
  • The @@ERROR function only indicates the error number that occurred in the last T-SQL statement that was executed.
  • If @@ERROR returns zero, then no error occurred.
  • A statement that causes a nonfatal error is simply skipped, and execution continues with the next statement in the procedure.
  • Nonfatal errors in a stored procedure called by an EXECUTE statement do not propagate up the call chain.

A basic strategy
In light of these four points, a basic strategy can be devised. In reality, it’s closer to no strategy at all, but to a degree the environment forces it on you.
  • It's impractical to check each line in a procedure for an error, so you should identify critical statements that have a high probability of failure and check only those.
  • Should you need to communicate a failure to a caller, you should make use of return codes or, better yet, wrap the call in a transaction.
  • If the success of subsequent statements depends on the success of the first, you'll have to end the procedure manually after detecting an error.
  • Count on the access provider to inform a client of an error.

Generating errors
You may want to throw an error manually with the RAISERROR statement if it’s obvious that continuing to process a procedure will itself cause an error. You can use RAISERROR to raise a custom error message or number back to the client. Below is the syntax for the use of RAISERROR, and Figure A explains the means of the various arguments.
RAISERROR ({msg_id|msg_str}{, severity, state}
[argument [,…n]])
[WITH option [, …n]]
Figure A

msg_id

Error code for the custom message. RAISERROR accepts any value of 13000 or over for this parameter, while custom messages should have a msg_id greater than or equal to 50000.

msg_str

A custom error message, should the message not appear in sysmessages. Supports replaceable variables specified by the argument parameter below. See documentation for details.

severity

The severity code for the message, anything from 0 to 25. Codes of 19-25 are typically fatal. Can only be used by members of the sysadmin server role and require the use of the WITH LOG option.

state

A code that represents some information about the state that caused the error. Not used internally by SQL Server.

argument

Values for any replaceable variables are defined in an error message.

WITH…

One of three custom options:
·     WITH LOG logs the error in the application and server error logs.
Must be used with a severity code of 19 or greater.

·     WITH NOWAIT sends the error immediately to the client.
·     WITH SETERROR sets @@ERROR to the value specified by msg_id, regardless of severity level.

RAISERROR arguments defined

The first thing you'll probably try to do with RAISERROR is raise a predefined error code. That may or may not work, depending on which error code you use. RAISERROR will accept error codes from 13000 on up, which excludes quite a few possible standard errors. So what good is it, then?

In fact, RAISERROR is really for sending custom error messages, and at that it excels. As you can see from Figure A, the msg_str identifier can contain whatever error message you want, so RAISERROR is handy for propagating ad hoc error messages back to a client.

Custom error messages
Of course, in most cases you'll use an error message more than once, and typing the same message over and over doesn't make much sense. It's possible, using the sp_addmessage procedure, to add a new custom error message to the sysmessages table. You can then refer to that new message's ID number when using RAISERROR.

Custom error messages must have identifier numbers greater than or equal to 50000, and the message itself is limited to 255 characters or less. As an example, to create a new error message, assign it an identifier number of 55555, and assign it a severity code of 10, you would call sp_addmessage like this:
sp_addmessage 55555, 10, 'New error message.'

You could then raise that new error in a procedure like this:
RAISERROR 55555, 10

It's worth noting that the error message supports replaceable parameters that can provide more information at runtime, using syntax very similar to the PRINTF formatting used in C. The documentation for RAISERROR gives a very good example of this.

Not hard, just different
Creating error-aware stored procedures isn't difficult; it's just different from what you're probably used to. With a good plan and a little knowledge of the basics, it's easy to do.

Editor's Picks