Error and Transaction Handling in SQL Server

Part One – Jumpstart Error Handling

Introduction

This article is the first in a series of three about error and transaction handling in SQL Server. The aim of this first article is to give you a jumpstart with error handling by showing you a basic pattern which is good for the main bulk of your code. This part is written with the innocent and inexperienced reader in mind, and for this reason I am intentionally silent on many details. The purpose here is to tell you how without dwelling much on why. If you take my words for your truth, you may prefer to only read this part and save the other two for a later point in your career.

On the other hand, if you question my guidelines, you certainly need to read the other two parts, where I go into much deeper detail exploring the very confusing world of error and transaction handling in SQL Server. Parts Two and Three, as well as the three appendixes, are directed towards readers with a more general programming experience, although necessarily not with SQL Server. This first article is short; Parts Two and Three are considerably longer.

Index of All Error-Handling Articles

Here follows a list of all articles in this series:

All the articles above are for SQL 2005 and later. For those who still are on SQL 2000, there are two older articles:

Why Error Handling?

Why do we have error handling in our code? There are many reasons. In a forms application we validate the user input and inform the users of their mistakes. These user mistakes are anticipated errors. But we also need to handle unanticipated errors. That is, errors that occur because we overlooked something when we wrote our code. A simple strategy is to abort execution or at least revert to a point where we know that we have full control. It cannot be enough stressed that it is entirely impermissible to ignore an unanticipated error. This is a sin that can have grave consequences: it could cause the application to present incorrect information to the user or even worse to persist incorrect data in the database. It is also important to communicate that an error has occurred, lest that the user thinks that the operation went fine, when your code in fact performed nothing at all.

In a database system, we often want updates to be atomic. For instance, say that the task is to transfer money from one account to another. To this end, we need to update two rows in the CashHoldings table and add two rows to the Transactions table. It's absolutely impermissible that an error or an interruption would result in money being deposited into the receiving account without it being withdrawn from the other. For this reason, in a database application, error handling is also about transaction handling. In this example, we need to wrap the operation in BEGIN TRANSACTION and COMMIT TRANSACTION, but not only that: in case of an error, we must make sure that the transaction is rolled back.

Essential Commands

We will start by looking at the most important commands that are needed for error handling. In Part Two, I cover all commands related to error and transaction handling.

TRY-CATCH

The main vehicle for error handling is TRY-CATCH, very reminiscent of similar constructs in other languages. The structure is:

BEGIN TRY
   <regular code>
END TRY
BEGIN CATCH
   <error handling>
END CATCH

If any error occurs in <regular code>, execution is transferred to the CATCH block, and the error-handling code is executed. Typically, your CATCH rolls back any open transaction and reraises the error, so that the calling client program understand that something went wrong. As for how to reraise the error, we will come to this later in this article.

Here is a very quick example:

BEGIN TRY
   DECLARE @x int
   SELECT @x = 1/0
   PRINT 'Not reached'
END TRY
BEGIN CATCH 
   PRINT 'This is the error: ' + error_message()
END CATCH

The output:

This is the error: Divide by zero error encountered.

We will return to the function error_message() later. It is worth noting that using PRINT in your CATCH handler is something you only would do when experimenting. You should never do so in real application code.

If <regular code> calls stored procedures or invokes triggers, any error that occurs in these will also transfer execution to the CATCH block. More exactly, when an error occurs, SQL Server unwinds the stack until it finds a CATCH handler, and if there isn't any, SQL Server sends the error message to the client.

There is one very important limitation with TRY-CATCH you need to be aware of: it does not catch compilation errors that occur in the same scope. Consider:

CREATE PROCEDURE inner_sp AS
   BEGIN TRY
      PRINT 'This prints'
      SELECT * FROM NoSuchTable
      PRINT 'This does not print'
   END TRY
   BEGIN CATCH
      PRINT 'And nor does this print'
   END CATCH
go
EXEC inner_sp

The output is:

As you see the TRY block is entered, but when the error occurs, execution is not transferred to the CATCH block as expected. This is true for all compilation errors such as missing columns, incorrect aliases etc that occur at run-time. (Compilation errors can occur at run-time in SQL Server due to deferred name resolution, a (mis)feature where SQL Server permits you to create a procedure that refers to non-existing tables.)

These errors are not entirely uncatchable; you cannot catch them in the scope they occur, but you can catch them in outer scopes. Add this code to the example above:

CREATE PROCEDURE outer_sp AS
   BEGIN TRY
      EXEC inner_sp
   END TRY
   BEGIN CATCH
      PRINT 'The error message is: ' + error_message()
   END CATCH
go
EXEC outer_sp

Now we get this output:

This time the error is caught because there is an outer CATCH handler.

SET XACT_ABORT ON

Your stored procedures should always include this statement in the beginning:

SET XACT_ABORT, NOCOUNT ON

This turns on two session options that are off by default for legacy reasons, but experience has proven that best practice is to always have them on. The default behaviour in SQL Server when there is no surrounding TRY-CATCH is that some errors abort execution and roll back any open transaction, whereas with other errors execution continues on the next statement. When you activate XACT_ABORT ON, almost all errors have the same effect: any open transaction is rolled back and execution is aborted. There are a few exceptions of which the most prominent is the RAISERROR statement.

The option XACT_ABORT is essential for a more reliable error and transaction handling. Particularly, with the default behaviour there are several situations where execution can be aborted without any open transaction being rolled back, even if you have TRY-CATCH. We saw one such example in the previous section where we learnt that TRY-CATCH does not catch compilations errors in the same scope. An open transaction which is not rolled back in case of an error can cause major problems if the application jogs along without committing or rolling back.

For good error handling in SQL Server, you need both TRY-CATCH and SET XACT_ABORT ON. Of these two, SET XACT_ABORT ON is the most important. For production-grade code it's not really sufficient to rely on XACT_ABORT, but for quick and simple stuff it can do.

The option NOCOUNT has nothing to do with error handling, but I included in order to show best practice. The effect of NOCOUNT is that it suppresses messages like (1 row(s) affected) that you can see in the Message tab in SQL Server Management Studio. While these row counts can be useful when you work interactively in SSMS, they can degrade performance in an application because of the increased network traffic. The row counts can also confuse poorly written clients that think they are real result sets.

Above, I've used a syntax that is a little uncommon. Most people would probably write two separate statements:

SET NOCOUNT ON
SET XACT_ABORT ON

There is no difference between this and the above. I prefer the version with one SET and a comma since it reduces the amount of noise in the code. As these statements should appear in all your stored procedures, they should take up as little space as possible.

General Pattern for Error Handling

Having looked at TRY-CATCH and SET XACT_ABORT ON, let's piece it together to a pattern that we can use in all our stored procedures. To take it slow and gentle, I will first show an example where I reraise the error in a simple-minded way, and in the next section I will look into better solutions.

For the example, I will use this simple table.

CREATE TABLE sometable(a int NOT NULL,
                       b int NOT NULL,
                       CONSTRAINT pk_sometable PRIMARY KEY(a, b))

Here is a stored procedure that showcases how you should work with errors and transactions.

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

The first line in the procedure turns on XACT_ABORT and NOCOUNT in single statement as I showed above. This line is the only line to come before BEGIN TRY. Everything else in the procedure should come after BEGIN TRY: variable declarations, creation of temp tables, table variables, everything. Even if you have other SET commands in the procedure (there is rarely a reason for this, though), they should come after BEGIN TRY.

The reason I prefer to have SET XACT_ABORT, NOCOUNT ON before BEGIN TRY is that I see this as one line of noise: it should always be there, but that I don't want it to strain my eyes. This is certainly a matter of preference, and if you prefer to put the SET commands after BEGIN TRY, that's alright. What is important is that you should never put anything else before BEGIN TRY.

The part between BEGIN TRY and END TRY is the main meat of the procedure. Because I wanted to include a user-defined transaction, I introduced a fairly contrived business rule which says that when you insert a pair, the reverse pair should also be inserted. The two INSERT statements are inside BEGIN and COMMIT TRANSACTION. In many cases you will have some lines code between BEGIN TRY and BEGIN TRANSACTION. Sometimes you will also have code between COMMIT TRANSACTION and END TRY, although that is typically only a final SELECT to return data or assign values to output parameters. If your procedure does not perform any updates or only has a single INSERT/UPDATE/DELETE/MERGE statement, you typically don't have an explicit transaction at all.

Whereas the TRY block will look different from procedure to procedure, the same is not true for the CATCH block. Your CATCH blocks should more or less be a matter of copy and paste. That is, you settle on something short and simple and then use it all over the place without giving it much thinking. The CATCH handler above performs three actions:

1. Rolls back any open transaction.
2. Reraises the error.
3. Makes sure that the return value from the stored procedure is non-zero.

These actions should always be there. Always. You may argue that the line

IF @@trancount > 0 ROLLBACK TRANSACTION

is not needed if there no explicit transaction in the procedure, but nothing could be more wrong. Maybe you call a stored procedure which starts a transaction, but which is not able to roll it back because of the limitations of TRY-CATCH. Maybe you or someone else adds an explicit transaction to the procedure two years from now. Will you remember to add the line to roll back then? Don't count on it. I can also hear readers that object if the caller started the transaction we should not roll back.... Yes, we should, and if you want to know why you need to read Parts Two and Three. Always rolling back the transaction in the CATCH handler is a categorical imperative that knows of no exceptions.

The code for reraising the error includes this line:

DECLARE @msg nvarchar(2048) = error_message()

The built-in function error_message() returns the text for the error that was raised. On the next line, the error is reraised with the RAISERROR statement. This is an unsophisticated way to do it, but it does the job. We will look at alternatives in the next chapter.

Note: the syntax to give variables an initial value with DECLARE was introduced in SQL 2008. If you are on SQL 2005, you will need to split the line in one DECLARE and one SELECT statement.

Always reraise? What if you only want to update a row in a table with the error message? Yes, that is a situation that occurs occasionally, although you would typically do that in an inner CATCH block which is part of a loop. (I have a longer example demonstrating this in Part Three.) The outer CATCH block in a procedure is exactly for catching and reraising unexpected errors you did not foresee. Dropping these errors on the floor is a criminal sin. Yes, They must be reraised.

The final RETURN statement is a safeguard. Recall that RAISERROR never aborts execution, so execution will continue with the next statement. As long as all procedures are using TRY-CATCH and likewise all client code is using exception handling this is no cause for concern. But your procedure may be called from legacy code that was written before SQL 2005 and the introduction of TRY-CATCH. In those days, the best we could do was to look at return values. What you return does not really matter, as long as it's a non-zero value. (Zero is usually understood as success.)

The last statement in the procedure is END CATCH. You should never have any code after END CATCH for the outermost TRY-CATCH of your procedure. For one thing, anyone who is reading the procedure will never see that piece of code.

Having read all the theory, let's try a test case:

EXEC insert_data 9, NULL

The output is:

Let's add an outer procedure to see what happens when an error is reraised repeatedly:

CREATE PROCEDURE outer_sp @a int, @b int AS
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      EXEC insert_data @a, @b
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH
go
EXEC outer_sp 8, 8

The output is:

We get the correct error message, but if you look closer at the headers of this message and the previous, you may note a problem:

The error messages give the location of the final RAISERROR statement that was executed. In the first case, only the line number is wrong. In the second case, the procedure name is incorrect as well. For simple procedures like our test procedures, this is not a much of an issue, but if you have several layers of nested complex stored procedures, only having an error message but not knowing where it occurred makes your troubleshooting a lot more difficult. For this reason, it is desirable to reraise the error in such a way that you can locate the failing piece of code quickly, and this is what we will look at in the next chapter.

Three Ways to Reraise the Error

Using error_handler_sp

We have seen error_message(), which returns the text for an error message. An error message consists of several components, and there is one error_xxx() function for each one of them. We can use this to reraise a complete message that retains all the original information, albeit with a different format. Doing this in each and every CATCH handler would be a gross sin of code duplication, and there is no reason to. You don't have to be in the CATCH block to call error_message() & co, but they will return exactly the same information if they are invoked from a stored procedures that your CATCH block calls.

Let me introduce to you error_handler_sp:

CREATE PROCEDURE error_handler_sp AS
 
   DECLARE @errmsg   nvarchar(2048),
           @severity tinyint,
           @state    tinyint,
           @errno    int,
           @proc     sysname,
           @lineno   int
           
   SELECT @errmsg = error_message(), @severity = error_severity(),
          @state  = error_state(), @errno = error_number(),
          @proc   = error_procedure(), @lineno = error_line()
       
   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                       ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)

The first thing error_handler_sp does is to capture the value of all the error_xxx() functions into local variables. (Exactly what all these mean, is something I am not covering in this introductory article, but I leave that for Part Two.) I will return to the IF statement in a second. Instead let's first look at the SELECT statement inside of it:

SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                 ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                 ltrim(str(@errno)) + ': ' + @errmsg

The purpose of this SELECT statement is to format an error message that we pass to RAISERROR, and which includes all information in the original error message which we cannot inject directly into RAISERROR. We need to give special treatment to the procedure name, since it will be NULL for errors that occur in ad-hoc batches or in dynamic SQL. Whence the use of the coalesce() function. (If you don't really understand the form of the RAISERROR statement, I discuss this in more detail in Part Two.)

The formatted error message starts with three asterisks. This serves two purposes: 1) We can directly see that this is a message reraised from a CATCH handler. 2) This makes it possible for error_handler_sp to filter out errors it has reraised once or more already with the condition NOT LIKE '***%' to avoid that error messages get modified a second time.

Here is how a CATCH handler should look like when you use error_handler_sp:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

Let's try some test cases.

EXEC insert_data 8, NULL
EXEC outer_sp 8, 8

This results in:

The header of the messages say that the error occurred in error_handler_sp, but the texts of the error messages give the original location, both procedure name and line number.

I will present two more methods to reraise errors. However, error_handler_sp is my main recommendation for readers who only read this part. It's simple and it works on all versions of SQL Server from SQL 2005 and up. There is really only one drawback: in some situations SQL Server raises two error messages, but the error_xxx() functions return only information about one of them, and thus one of the error messages is lost. This can be quite difficult with administrative commands like BACKUP/RESTORE, but it is rarely an issue in pure application code.

Using ;THROW

In SQL 2012, Microsoft introduced the ;THROW statement to make it easier to reraise errors. Unfortunately, Microsoft made a serious design error with this command and introduced a dangerous pitfall.

With ;THROW you don't need any stored procedure to help you. Your CATCH handler becomes as simple as this:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ;THROW
   RETURN 55555
END CATCH

The nice thing with ;THROW is that it reraises the error message exactly as the original message. If there were two error messages originally, both are reraised which makes it even better. As with all other errors, the errors reraised by ;THROW can be caught in an outer CATCH handler and reraised. If there is no outer CATCH handler, execution is aborted, so that RETURN statement is actually superfluous. (I still recommend that you keep it, in case you change your mind on ;THROW later.)

If you have SQL 2012 or later, change the definition of insert_data and outer_sp, and try the tests cases again. The output this time:

The procedure name and line number are accurate and there is no other procedure name to confuse us. Also, the original error numbers are retained.

At this point you might be saying to yourself: he must be pulling my legs, did Microsoft really call the command ;THROW? Isn't it just THROW? True, if you look it up in Books Online, there is no leading semicolon. But the semicolon must be there. Officially, it is a terminator for the previous statement, but it is optional, and far from everyone uses semicolons to terminate their T‑SQL statements. More importantly, if you leave out the semicolon before THROW this does not result in a syntax error, but in a run-time behaviour which is mysterious for the uninitiated. If there is an active transaction you will get an error message – but a completely different one from the original. Even worse, if there is no active transaction, the error will silently be dropped on the floor. Something like mistakenly leaving out a semicolon should not have such absurd consequences. To reduce the risk for this accident, always think of the command as ;THROW.

It should not be denied that ;THROW has its points, but the semicolon is not the only pitfall with this command. If you want to use it, I encourage you to read at least Part Two in this series, where I cover more details on ;THROW. Until then, stick to error_handler_sp.

Using SqlEventLog

The third way to reraise an error is to use SqlEventLog, which is a facility that I present in great detail in Part Three. Here I will only give you a teaser.

SqlEventLog offers a stored procedure slog.catchhandler_sp that works similar to error_handler_sp: it uses the error_xxx() functions to collect the information and reraises the error message retaining all information about it. In addition, it logs the error to the table slog.sqleventlog. Depending on the type of application you have, such a table can be a great asset.

To use SqlEventLog, your CATCH hander would look like this:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC slog.catchhandler_sp @@procid
   RETURN 55555
END CATCH

@@procid returns the object id of the current stored procedure, something that SqlEventLog uses when it writes the log information to the table. Using the same test cases, this is the output with catchhandler_sp:

As you see, the error messages from SqlEventLog are formatted somewhat differently from error_handler_sp, but the basic idea is the same. Here is a sample of what is logged to the table slog.sqleventlog:

If you want to play with SqlEventLog right on the spot, you can download the file sqleventlog.zip. For installation instructions, see the section Installing SqlEventLog in Part Three.

Final Remarks

You have now learnt a general pattern for error and transaction handling in stored procedures. It is not perfect, but it should work well for 90-95 % of your code. There are a couple of limitations you should be aware of:

1. As we have seen, compilation errors such as missing tables or missing columns cannot be trapped in the procedure where they occur, only in outer procedures.
2. The pattern does not work for user-defined functions, since neither TRY-CATCH nor RAISERROR are permitted there.
3. When you call a stored procedure on a linked server that raises an error, this error may bypass the error handler in the procedure on the local server and go to directly to the client.
4. When a procedure is called by INSERT-EXEC, you will get an ugly error, because ROLLBACK TRANSACTION is not permitted in this case.
5. As noted above, if you use error_handler_sp or SqlEventLog, you will lose one error message when SQL Server raises two error messages for the same error. This is not an issue with ;THROW.

I cover these situations in more detail in the other articles in the series.

Before I close this off, I like to briefly cover triggers and client code.

Triggers

The pattern for error handling in triggers is not any different from error handling in stored procedures, except in one small detail: you should not include that RETURN statement. (Because RETURN with a value is not permitted in triggers.)

What is important to understand about triggers is that they are part of the command that fired the trigger, and in a trigger you are always in a transaction, even if you did not use BEGIN TRANSACTION. Sometimes I see people in SQL Server forums ask if they can write a trigger that does not roll back the command that fired the trigger if the trigger fails. The answer is that there is no way that you can do this reliably, so you better not even try. If you have this type of requirement, you should probably not use a trigger at all, but use some other solution. In Parts Two and Three, I discuss error handling in triggers in more detail.

Client Code

Yes, you should have error handling in client code that accesses the database. That is, you should always assume that any call you make to the database can go wrong. Exactly how to implement error handling depends on your environment, and to cover all possible environments out there, I would have to write a couple of more articles. And learn all those environments.

Here, I will only point out one important thing: your reaction to an error raised from SQL Server should always be to submit this batch to avoid orphaned transactions:

IF @@trancount > 0 ROLLBACK TRANSACTION

This also applies to the famous message Timeout expired (which is not a message from SQL Server, but the client API).

I cover error handling in ADO .NET in the last chapter of Part 3. If you use old ADO, I cover this in my old article on error handling in SQL 2000.

End of Part One

This is the end of Part One of this series of articles. If you just wanted to learn the pattern quickly, you have completed your reading at this point. If your intention is to read it all, you should continue with Part Two which is where your journey into the confusing jungle of error and transaction handling in SQL Server will begin for real.

If you have questions, comments or suggestions specific to this article, please feel free to contact me at [email protected]. This includes small things like spelling errors, bad grammar, errors in code samples etc. Since I don't have a publisher, I need to trust my readership to be my tech editors and proof-readers. :-) If you have questions relating to a problem you are working with, I recommend that you ask that question in a public forum, as this is more likely to give you a quick response.

For a list of acknowledgements, please see the end of Part Three. Below is a revision history for Part One.

...and don't forget to add this line first in your stored procedures:

SET XACT_ABORT, NOCOUNT ON

Revision History

2015-05-03

First version.

Back to my home page.