Browse DevX
Sign up for e-mail newsletters from DevX


Handling SQL Server Errors in Nested Procedures

Transact-SQL error handling can become very complex when you have to trap errors in nested stored procedures that use transactions. The complexity arises from restrictions on how stored procedures can use transactions and from the asymmetry between the Transact-SQL COMMIT and ROLLBACK statements.


asic error handling in SQL Server's programming language, Transact-SQL, is straightforward. But when you nest calls to stored procedures, and the procedures have SQL transactions, error handling becomes much more complex. In this article I'll show you some tried-and-true models for how to handle errors in nested stored procedures with transactions. Over the past year I've worked on two projects that needed a strategy for handling errors in stored procedures where the procedures used transactions. My task was to come up with a way to gracefully exit from the stored procedures when non-fatal errors were detected so I could roll back the transaction. I'll present the result of that work in the two models you'll learn about in this article.

So how do you handle errors in your code when an error occurs? Unless it encounters a broken connection, SQL Server will return an error to the client application. When you work directly with your own client or middle-tier code, you have much more control over how you handle errors. So you could just issue all your queries to SQL Server discretely from your client code and let SQL Server errors throw you into your error-catching logic, thereby keeping all your error handling in your own calling code. However, encapsulating database-oriented code in SQL Server stored procedures offers a more efficient and elegant solution. In those cases, you need to consider what to do when SQL Server errors occur.

Let's look first at some general features of error handling.

Transact-SQL Error Handling
Transact-SQL error handling techniques are simple, but SQL Server's error-returning behavior can seem confusing and occasionally inconsistent. You can trap some errors in Transact-SQL code, but other errors are fatal to a batch or transaction. When SQL Server encounters a non-fatal error trying to execute a command, the @@ERROR system function captures the error message. If the error is fatal, you cannot catch the error in Transact-SQL at all; you'll have to rely on your client code's catch logic.

If a trappable error occurs, the @@ERROR function will have a value greater than 0. SQL Server resets the @@ERROR value after every successful command, so you must immediately capture the @@ERROR value. Most of the time, you'll want to test for changes in @@ERROR right after any INSERT, UPDATE, or DELETE statement. I prefer to capture the value of @@ERROR into a variable immediately, so I can use it later, as in the following snippet:

   DECLARE @Error int
   UPDATE ...
   SET @Error = @@ERROR
   IF @Error > 0 ...

Because SQL Server resets the @@ERROR with the next successful command, when the IF statement in the code snippet successfully executes, SQL Server will reset @@ERROR back to 0. This can cause a problem if you're also interested in getting the row count of a command, because most commands will also reset the @@ROWCOUNT system. You can capture them both simultaneously using the SELECT statement as shown in the following snippet:

   DECLARE @Error int, @Rowcount int
   UPDATE ...
   SELECT @Error = @@ERROR
     ,@Rowcount = @@ROWCOUNT
   IF @Error > 0 ...
     IF @Rowcount = 0 ...

You can also capture @@ERROR to test for SELECT errors, with some limitations. For example, you can trap an error if at runtime some object (table or view) referenced by the SELECT is missing (Transact-SQL error message 208.) However, syntax errors in the SELECT command, or any other error that causes compilation of a batch to stop, are fatal and cannot be trapped.

It's much more efficient and elegant to encapsulate database-oriented code in SQL Server stored procedures, so you need to consider what to do when errors occur in your stored procedures.
It's more useful to capture @@ERROR after INSERT, UPDATE, and DELETE statements because, by default, constraint violations are not fatal. Foreign key and check constraints will not be fatal (meaning they will not abort the batch or transaction) unless SET XACT_ABORT is ON (see the section on XACT_ABORT below.)

The number of possible error messages is very large; over 3,800 error messages are stored in the master database's sysmessages system table (some are actually templates). Unfortunately, only a small number of the error messages are documented in Books Online; you can often get more complete explanations of errors in the Knowledge Base.

You can use the RAISERROR statement to generate your own errors in Transact-SQL code. You can also define your own error messages, starting with number 50001, using the system stored procedure sp_addmessage, which will add a message to the sysmessages table. You can then reference the error message in the RAISERROR statement. However, most developers prefer to insert a string message into the RAISERROR statement, because adding custom messages to the sysmessages table creates an additional dependency of your database on a table outside the database, thereby making it less portable.

Batches and Stored Procedures
SQL Server compiles and executes its code in batches of commands. When you work with SQL Server scripts, you use the GO statement for separating batches (it is not really an executed command.) Every stored procedure, trigger, and user-defined function can each consist of only one batch. SQL Server has some important restrictions on batches. For example, you must make the CREATE PROCEDURE the first statement in a batch, so you can create only one procedure per batch.

It's possible that an SQL Server error may abort the current batch (stored procedure, trigger, or function) but not abort a calling batch. In this article, I will focus primarily on stored procedures, with some remarks about triggers in the context of transactions.

You should consider two major points when you work with SQL Server stored procedures and errors:

  • Does an SQL Server error abort a called batch or stored procedure?
  • Does the error abort a set of nested (called) stored procedures?

Thanks for your registration, follow us on our social networks to keep up-to-date