Sql error handling

The goal of this article is to provide a simple and easy to use error handling mechanism with minimum complexity. This article is completely compatible with MS SQL Server 2012 and above versions.

Problem Definition

There are many questions in
MSDN forum and other Internet communities about Error Handling in SQL Server. There are several issues as presented in the Table of Contents above.

Introduction

There are many articles written by the best experts and there are complete references about Error Handling in SQL Server. The goal of this article is to provide a simple and easy to use error handling mechanism with minimum complexity. Therefore I will try
to address this topic from a problem-solving approach and particularly in SQL Server 2012 (and later) versions. So the road map of this article is to cover the above questions as well as providing a step by step tutorial to design a structured mechanism for
error handling in SQL Server 2012 (and up) procedures. 

Solution

Is there any structured Error Handling mechanism in SQL Server?

Yes, there is. The TRY/CATCH construct is the structured mechanism for error handling in SQL Server 2005 and later. This construct has two parts; we can try executing some statements in
TRY block and handling errors in the CATCH block if they occur. Therefore, the simplest error handling structure can be like this:

• TRY
  • Try executing statements  
• CATCH
  • Handle the errors if they occur

Here is a sample code to provide the above structure in the simplest form:

—result

Will all statements in TRY block try to execute?

When executing statements in the TRY block, if an error occurs the flow of execution will transfer to the
CATCH block. So the answer is NO!

We can see this behavior with an example. As we can see after executing the following code, the statement no. 3 does not try executing, because the flow of execution will transfer to the CATCH block as soon as statement no. 2 raises
an error.

—result

Does the CATCH part automatically handle the errors?

No. The role of the TRY/CATCH construct is just providing a mechanism to try executing SQL statements. Therefore, we need to use another approach to handle the errors in the CATCH block that I’ll explain later. For instance, the following code will try to
execute a divide by zero statements. It does not automatically handle any errors. In fact, in this sample code, when an error occurs the flow control immediately transfers to the CATCH block, but in the CATCH block, we do not have any statement to tell us
that there was an error!

—result

In the CATCH block we can handle the error and send the error message to the application. So we need an element to show what error occurs. This element is
RAISERROR. So the error handling structure could be like this:

• TRY
  • Try executing statements 
• CATCH
  • Handle the error if occurs
    • RAISERROR

Here is sample code to produce the above structure:

—result

The RAISERROR itself needs other elements to identify the error number, error message, etc. Now we can complete the error handling structure:

• TRY
  • Try executing statements 
• CATCH
  • Handle the error if occurs
    • RAISERROR
      • ERROR_NUMBER()
      • ERROR_MESSAGE()
      • ERROR_SEVERITY()
      • ERROR_STATE()
      • ERROR_PROCEDURE()
      • ERROR_LINE()

Here is sample code to produce the above structure:

 —result

Is it a good idea to use a general procedure as a modular Error Handler routine?

From a modular programming approach, it’s recommended to create a stored procedure that does the RAISERROR job. But I believe that using a modular procedure (I call it spErrorHandler) to re-raise errors is not a good idea. Here are my reasons:

1. When we call RAISERROR in procedure “spErrorHandler”, we have to add the name of the procedure that the error occurs within to the Error Message. This will confuse the application end-users
(Customer). Customer does not want to know which part of his car is damaged. He prefers that his car just send him a simple message which tells him there is an error in its functions. In the software
world it’s more important to send a simple (English) message to the customer because if we send a complex error message, he will be afraid of what will happen to his critical data!

2. If we accept the first reason and decide to resolve this issue, we need to send a simple message to the client application. So we will lose the procedure name that the error occurs within and other useful information for debugging unless
we insert this useful information in an Error-Log table.

You can test this scenario with the following code:

 —result

As is illustrated in this figure, when using spErrorHandler, the values of ERROR_PROCEDURE() and ERROR_NUMBER() are changed in the output. This behavior is because of the RAISERROR functionality. This function always re-raises
the new exception, so spErrorHandler always shows that the value of ERROR_PROCEDURE() simply is “spErrorHandler”. As I said before there are two workarounds to fix this issue. First is concatenating this useful data with the error message and raise it, which
I spoke about in reason one. Second is inserting this useful data in another table just before we re-raise the error in spErrorHandler.

Now, we test the above sample without using spErrorHandler:

 —result

As you see in this figure, the procedure name and error number are correct. By the way, I prefer that if one customer reports an error, I go for SQL Server Profiler, simulate the environment completely, and test those SQL statements
in SSMS to recreate the error and debug it based on the correct error number and procedure name.

In the THROW section, I will explain that the main advantage of THROW over RAISERROR is that it shows the correct line number of the code that raises the error, which is so helpful for a developer in debugging his code.

3. Furthermore, with the THROW statement introduced in SQL SERVER 2012, there is no need to write extra code in the CATCH block. Therefore there is no need to write a separate procedure except for tracking the errors in another error log table.
In fact, this procedure is not an error handler, it’s an error tracker. I  will explain the THROW statement in the next section.

What are the benefits of THROW when we have RAISERROR?

The main objective of error handling is that the customer knows that an error occurred and reports it to the software developer. Then the developer can quickly realize the reason for the error and improve his code. In fact, error handling is a mechanism
that eliminates the blindness of both customer and developer.

To improve this mechanism Microsoft SQL Server 2012 introduced the THROW statement. Now I will address the benefits of THROW over RAISERROR.

The correct line number of the error!

As I said earlier this is the main advantage of using THROW. The following code will demonstrate this great feature:

 —result

As you can see in this figure, the line number of the error that RAISERROR reports to us always is the line number of itself in the code. But the error line number reported by THROW is line 6 in this example, which is the line
where the error occurred.

Easy to use

Another benefit of using the THROW statement is that there is no need for extra code in RAISERROR.

Complete termination

The severity level raised by THROW is always 16. But the more important feature is that when the THROW statement in a CATCH block is executed, then other code after this statement will never run.

The following sample script shows how this feature protects the code compared to RAISERROR:

 —result

Independence of sys.messages

This feature makes it possible to re-throw custom message numbers without the need to use
sp_addmessage to add the number.The feature is in real time, as you can see
in this code:

 —result

Tip

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

I want to check a condition in the TRY block. How can I control the flow of execution and raise the error?

This is a simple job! Now I change this question to this one:

“How can I terminate the execution of the TRY block?”

The answer is using THROW in the TRY block. Its severity level is 16, so it will terminate execution in the TRY block. We know that when any statement in the TRY block terminates (encounters an error) then immediately execution
goes to the CATCH block. In fact, the main idea is to THROW a custom error as in this code:

  —result

As you can see, we handle the error step by step. In the next session, we will complete this structure.

Does the CATCH part automatically rollback the statements within the TRY part?

This is the misconception that I sometimes hear. I will explain this problem with a little example. After executing the following code the table “dbo.Saeid” still exists. This demonstrates that the TRY/CATCH block does not implement
implicit transactions.

—result

Can someone use TRANSACTION in the TRY/CATCH block?

The previous question showed that if we want to rollback entire statements in a try block, we need to use explicit transactions in the  TRY block. But the main question here is:

“Where is the right place to commit and rollback? “

It’s a complex discussion that I would not like to jump into in this article. But there is a simple template that we can use for procedures (not triggers!).

This is that template:

—result

The elements of this structure are:

• TRY block
  • XACT_ABORT
  • Begin transaction
    • Statements to try
  • Commit transaction
• CATCH block
  • Check @@TRANCOUNT and rollback all transactions
  • THROW

Here is a short description of two parts of the above code:

XACT_ABORT

In general, it’s recommended to set the XACT_ABORT option to ON in our TRY/CATCH block in procedures. By setting this option to ON if we want to roll back the transaction, any user-defined transaction is rolled back.

@@TRANCOUNT

We check this global variable to ensure there is no open transaction. If there is an open transaction it’s time to execute rollback statements. This is a must in all CATCH blocks, even if you do not have any transactions in that
procedure. An alternative is to use XACT_STATE(). 

Conclusion

Introduction of the THROW statement is a big feat in Error Handling in SQL Server 2012. This statement enables database developers to focus on accurate line numbers of the procedure code. This article provided
a simple and easy to use error handling mechanism with minimum complexity using SQL Server 2012. By the way, there are some more complex situations that I did not cover in this article. If you need to dive deeper, you can see the articles in the
See Also section.

BOL link http://technet.microsoft.com/en-us/library/ms175976.aspx

See Also

Related Wiki Articles

Other Languages

• Mecanismo de Tratamento de erros em SQL Server 2012 (pt-BR)

The ability to handle errors is essential in any programming language and, naturally, we have to implement safe error handling in our T-SQL if we want to build solid SQL Server code. SQL Server 2005, and later, superseded the old style @@Error error handling, with the TRY…CATCH blocks that are more familiar to Java and C# programmers.

While use of TRY…CATCH certainly is the best way to handle errors in T-SQL, it is not without difficulties. Error handling in T-SQL can be very complex, and its behavior can sometimes seem erratic and inconsistent. Furthermore, error handling in Transact SQL lacks many features that developers who use languages such as Java and C# take for granted. For example, in SQL Server 2005 and 2008, we cannot even re-throw an error without changing its error code. This complicates handling errors, because we have to write separate conditions for detecting exceptions caught for the first time, and for detecting re-thrown exceptions.

This article will demonstrate simple SQL Server error handling, using XACT_ABORT and transactions, describe the most common problems with TRY…CATCH error handling, and advocate that the defensive programmer, where possible, implements only simple error checking and handling in SQL Server, with client-side error handling used to enforce what is done on the server.

Prepare for Unanticipated Failure

Any statement can, and at some point inevitably will, fail. This may seem to be a statement of the obvious, but too many programmers seem to assume that once their code “works” then the data modifications and queries that it contains will always succeed.

In fact, data modifications can and do fail unexpectedly. For example, the data may not validate against a constraint or a trigger, or the command may become a deadlock victim. Even if the table does not have any constraints or triggers at the time the code is developed, they may be added later. It is wise to assume that our modifications will not always succeed.

Many queries, too, can fail. Just as a modification can become a deadlock victim, so can a SELECT (unless that SELECT is running under either of the two snapshot isolation levels). If a SELECT statement utilizes a user-defined function, then errors may occur in that function that will cause the query to fail. Other common causes of failure are queries that attempt to use a temporary table that does not exist, or contain subqueries that return more than one value.

Listing 1-1 demonstrates a very simple case of a SELECT statement that may succeed or fail depending on locale settings.

Listing 1-1: Language settings can cause certain date queries to fail

The main point is clear: when we develop T-SQL code, we cannot assume that our queries or data modifications will always succeed and we need to be prepared for such failures and handle them gracefully. When an unexpected error occurs during data modification, it is essential that execution of the statement is terminated, the database is returned to the state it was in before the statement started, and a message is sent to the calling client, giving some details of the error that occurred and the action taken as a result. Likewise, if a SELECT fails that is part of a longer transaction that has already modified data then these modifications must be undone as well.

Using Transactions for Data Modifications

In many cases, during data modifications, we cannot take our database from one consistent state to another in a single statement. If a data modification requires more than one statement to effect the required change, then explicit transactions should be used to ensure that these statements succeed or fail as a unit, and that our error handling can handle them as a unit.

For example, suppose that we need to log in one table all the modifications made to another table. Listing 1-2 shows the code to create the table to be modified (Codes) and the table in which the modifications will be logged (CodeDescriptionsChangeLog).

Listing 1-2: The Codes and CodeDescriptionsChangeLog tables

Note that the log table does not have a FOREIGN KEY constraint referring to the Codes table, because the log records need to be kept even if we delete the corresponding rows in Codes.

The procedure shown in Listing 1-3 modifies the Codes table, and logs the change in the CodeDescriptionsChangeLog table.

Listing 1-3: The ChangeCodeDescription stored procedure

Listing 1-4 runs a simple smoke test on the new procedure.

Listing 1-4: A smoke test on the ChangeCodeDescription stored procedure

It looks like the procedure works, right? Note, however, that this stored procedure does not attempt to determine whether or not either of the two modifications failed, and it does not handle possible errors. Of course, one might argue that this stored procedure, could be a component of a perfectly valid system, if it is invoked by an application that does all the error handling. However, that does not make it, as is, a valid component. There’s a huge risk that a developer who builds another application may find this procedure and decide to call it, unaware of the required error handling in the calling procedure.

It may seem that nothing could possibly go wrong during these two trivial modifications, but we still cannot assume that both modifications will always succeed. In fact, even in this trivial example, we can devise a case where one modification can fail: if two modifications occur simultaneously, we may get a primary key violation on the CodeDescriptionsChangeLog table.

Rather than reproduce that case here, we can prove the same point simply by creating a CHECK constraint that prohibits inserts and updates against the CodeDescriptionsChangeLog table, and demonstrates what happens when one of our modifications fails and we do nothing to detect and handle it.

Listing 1-5: An INSERT into CodeDescriptionsChangeLog fails, but the UPDATE of Codes succeeds, and we end up with an UPDATE that has not been logged

In order to avoid this situation, we need to begin a transaction, attempt to do both modifications, determine whether or not both completed successfully, and commit the transaction only if both modifications succeeded. If either modification failed, we need to rollback the transaction, as part of our error handling. T-SQL allows several ways to accomplish that. Let’s begin with the simplest approach: using transactions along with the XACT_ABORT setting.

Using Transactions and XACT_ABORT to Handle Errors

In many cases, we do not need sophisticated error handling. Quite frequently all we need to do, in case of an error, is roll back all the changes and throw an exception, so that the client knows that there is a problem and will handle it. In such situations, a perfectly reasonable approach is to make use of the XACT_ABORT setting.

By default, in SQL Server this setting is OFF, which means that in some circumstances SQL Server can continue processing when a T-SQL statement causes a run-time error. In other words, for less severe errors, it may be possible to rollback only the statement that caused the error, and to continue processing other statements in the transaction.

If XACT_ABORT is turned on, SQL Server stops processing as soon as a T-SQL run-time error occurs, and the entire transaction is rolled back. When handling unexpected, unanticipated errors, there is often little choice but to cease execution and rollback to a point where there system is in a ‘known state’. Otherwise you risk seeing partially completed transactions persisted to your database, and so compromising data integrity. In dealing with such cases, it makes sense to have XACT_ABORT turned ON.

Data Modifications via OLDE DB
Note that in some cases XACT_ABORT is already set to ON by default. For example, OLE DB will do that for you. However, it is usually preferable to explicitly set it, because we do not know in which context our code will be used later.

Listing 1-6 illustrates a basic error handling approach, whereby our modifications take place within an explicit transaction, having set XACT_ABORT to ON. The PRINT commands in the procedure are for demonstration purposes only; we would not need them in production code.

Listing 1-6: Using the XACT_ABORT setting and an explicit transaction

Note that although we want to roll back all the changes if an error occurs, we do not need to explicitly determine if there are any errors, and we do not need to explicitly invoke ROLLBACK in our code; when XACT_ABORT is set to ON, it all happens automatically. Listing 1-7 tests our altered stored procedure.

Listing 1-7: Testing the altered stored procedure

As we have seen, the stored procedure worked perfectly well. Of course, this is just the first in as series of tests we should perform on our stored procedure. Complete testing would include:

• Making sure that if both the modification of the Codes table and the INSERT into the CodeDescriptionsChangeLog table succeed, then the transaction commits and both changes persist.
• Verifying that if an UPDATE of the Codes table fails then the transaction rolls back. To reproduce a failure, we can use a similar technique; a CHECK constraint that makes sure all UPDATEs against Codes table fail.
• Invoking the stored procedure without an outstanding transaction, when @@TRANCOUNT is 0. In that case we shall have to explicitly drop the CHECK constraint which we create in our test.

I encourage you to tweak Listing 1-7 and try out these other tests. In many cases, this simple approach of setting XACT_ABORT to ON and using an explicit transaction for modifications gets the job done without much effort. We should use this simple and robust approach unless we really need more sophisticated functionality from our error handling.

If we really want to do some more complex error handling on the server, using T-SQL, then we should use TRY…CATCH blocks, which are available in SQL Server 2005 and upwards.

Using TRY…CATCH blocks to Handle Errors

To handle errors in T-SQL modules, in SQL Server 2005 and upwards, we can use TRY…CATCH blocks. If any command inside the TRY block raises an error, the execution of the TRY block terminates immediately, which is similar to the behavior under XACT_ABORT setting. But, unlike with XACT_ABORT where the whole batch terminates, only the execution of the code inside the TRY block terminates, and the CATCH block begins to execute.

In cases where you are aware that a certain specific error could occur, then your error handling strategy can be different. You may attempt to add code to your CATCH block that corrects the error, or at least allows processing to continue. In these cases, it makes more sense to have XACT_ABORT set to OFF, so that you can handle the errors, and inform the calling client of what happened, without rolling back the entire batch.

As will become clear as we progress, my current philosophy is that all but the simplest error handling should be implemented, ideally, in a client-side language where the error handling is more robust and feature-rich than it is in SQL Server TRY…CATCH.

Therefore my goal here is not to cover TRY…CATCH in full detail, but to set out, with examples, some of the reasons why error handling in T-SQL can be complex and a little bewildering. I really want to encourage you to either fully understand all the ins and outs of T-SQL error handling, or to not to use it at all, except in the simplest cases.

Erland Sommarskog’s website, http://www.sommarskog.se/, is an excellent source of information on error handling. The book “Expert SQL Server 2005 Development” by Adam Machanic, Hugo Kornelis, and Lara Rubbelke is another great resource.

Finally, note that I do not cover “old-style” error handling, using @@ERROR, at all in this chapter. Use of @@ERROR has some well-known problems, such as inability to handle errors raised by triggers, and the fact that sometimes SQL Server simply fails to set its value correctly. In general, my advice would be to upgrade from @@ERROR to TRY…CATCH, or even better to client-side error handling for all but the simplest cases, as soon as possible.

A TRY…CATCH Example: Retrying After Deadlocks

Sometimes, it may make sense to use TRY…CATCH blocks to retry the execution of a statement, after a deadlock. One must exercise caution when doing so as retrying an UPDATE statement in this manner may lead to lost updates, as we discuss in detail in Chapter 10 of my book, Surviving Concurrent Modifications. The defensive programmer must take all possible measures to ensure that the possibility of deadlocks is minimized but, in some cases, it may be deemed acceptable, in the short term at least, to automatically retry after a deadlock.

In order to provide an example that you can run on your server, we’ll alter our ChangeCodeDescription stored procedure, as shown in Listing 1-8, so that it is high likely to be chosen as a deadlock victim, if it embraces in a deadlock with a competing session. Our goal here is not to demonstrate how to develop stored procedures that are unlikely to embrace in deadlocks, but to see how to use a TRY…CATCH block to retry after a deadlock.

If processing switches to our CATCH block, we will attempt to re-execute our transaction once more, in response to a deadlock; otherwise we will simply re-throw the error so that the calling client is notified and can respond.

Listing 1-8: Altering the ChangeCodeDescription stored procedure so that it retries after a deadlock

Before we run our test, let’s reset the test data in our Codes and CodeDescriptionsChangeLog tables.

Listing 1-9: Resetting the test data

We’re now ready to run the test. From one tab in SSMS, we’ll start a SERIALIZABLE transaction against the CodeDescriptionsChangeLog table, as shown in Listing 1-10.

Listing 1-10: Tab #1, Start a transaction against the CodeDescriptionsChangeLog table

From a second tab, invoke our stored procedure, as shown in Listing 1-11. The session will ‘hang’ in lock waiting mode, due to our SERIALIZABLE transaction accessing the CodeDescriptionsChangeLog table.

Listing 1-11: Tab #2, Invoke the ChangeCodeDescription stored procedure

Now return to Tab #1, and execute the commented UPDATE against the Codes table, from Listing 1-10, including the COMMIT. As soon as this code tries to execute, a deadlock is detected. SQL Server chooses our stored procedure execution from Tab #2 as the deadlock victim, since we deliberately contrived for this to be the case. The transaction in our TRY block is rolled back, but then our CATCH block is executed and we try to execute our stored procedure again. This time, since Tab #1 has now committed, the modification succeeds. The output from Tab #2 is shown in Listing 1-12.

Listing 1-12. Tab #2, output from execution of the stored procedure

Note also however, that the UPDATE we execute form Tab#1 is ‘lost’; its changes were overwritten when the re-try succeeded.

From these examples, we have learned the following:

• If several modifications must succeed or fail together, use transactions, and roll the modification back, as a unit, if any one of them fails
• Always anticipate that any modification may fail. Use XACT_ABORT to ensure that transactions roll back after a failure. Alternatively, we can wrap our transactions in TRY blocks, and roll them back in CATCH blocks.

Unfortunately, there are a few problems with using TRY…CATCH error handling that we need to discuss. In the next section, we’ll look at some ways in which TRY…CATCH error handling is limited and its behavior surprising. We’ll then see what we can achieve when using C# for error handling, instead of T-SQL.

TRY…CATCH Gotchas

T-SQL is not really an efficient language for error handling, and is certainly less robust than error handling in client side languages such as C++, Java, and C#. As such, although in most cases TRY…CATCH blocks work as expected and catch errors as they should, there are also quite a few “special cases” that we need to know about, where the behavior is not as we might expect.

Furthermore, TRY…CATCH error handling does not really facilitate code reuse. If we want to use this approach in another stored procedure, we cannot fully reuse our T-SQL error handling code; we have to cut and paste much of it into that other stored procedure. This is a recipe for bugs and inconsistencies.

Over the following sections, we’ll discuss some of the special cases of which we need to be aware, when using TRY…CATCH.

Re-throwing Errors

In many cases, we do not wish to handle certain errors in our CATCH block, and instead want to re-throw them, so that they are handled elsewhere. In our previous example, where we wished to retry execution after a deadlock, all other errors were handled by capturing the error message, using the ERROR_MESSAGE function, and re-throwing the error using RAISERROR. However, the error message on its own is generally insufficient; we should also retrieve the information from the ERROR_LINE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE functions, declare variables to store this information, and then use RAISERROR to re-throw it. This is very verbose and, as we shall see later, we can achieve exactly the same outcome in C# by issuing one single command: throw.

However, the real problem with the TRY…CATCH approach is this: RAISERROR cannot preserve ERROR_NUMBER, so when we re-throw an error we often change its error code. For example, consider the ConversionErrorDemo stored procedure in Listing 1-13. It attempts to cast a string as an integer in the TRY block, and then in the CATCH block invokes two of the seven error handling functions and re-throws the error.

Listing 1-13: An error with error number 245, which gets a different ERROR_NUMBER, 50000, when re-thrown.

The fact that re-thrown errors get a different error number means that, when we actually come to handling conversion errors, both re-thrown and original, we cannot catch then using the  error number alone, as shown in Listing 1-14.

Listing 1-14: The re-thrown error is no longer assigned number 245.

To catch both the original and re-thrown error, we need to parse the error message, as shown in Listing 1-15.

Listing 1-15: Parsing the error message to catch a re-thrown error.

Although. This time, we did catch our re-thrown error, our method is not robust: we can by mistakenly catch other errors and handle them as if they were conversion errors, as shown in Listing 1-16.

Listing 1-16: Incorrectly handling a ticket-saving error as if it were a conversion error.

As we have seen, the inability of T-SQL to re-throw errors may prevent us from robustly handling re-thrown errors. If we need to re-throw errors, we should do it on the client.

TRY…CATCH Blocks Cannot Catch all Errors

Interestingly enough, sometimes TRY…CATCH blocks just do not catch errors. Sometimes, this represents “expected behavior”; in other words, the behavior is documented and the reason why the error is not caught, for example when a connection fails, is intuitive. However, in some other cases the behavior, while still documented, can be quite surprising.

In either case, however, it means that we cannot assume that all errors originating in the database can or will be handled in a TRY…CATCH. Whenever we issue an SQL statement from the client, we need to be aware that it can generate an exception, and we need to be ready to handle it on the client, in case the TRY…CATCH blocks that we use in our T-SQL code doesn’t catch it.

Killed Connections and Timeouts

In some cases, it is the expected behavior that errors cannot be caught by TRY…CATCH blocks. For example, if your connection is killed, it is documented and well known that your CATCH block will not catch and handle it.

Also, we need to be aware of ‘attentions’, also known as ‘timeouts’, as they also cannot be caught by TRY…CATCH blocks, and this is also the expected behavior. To demonstrate this, start the script in Listing 1-17, but cancel its execution immediately by pressing the “Cancel Executing Query” button.

Listing 1-17: TRY…CATCH behavior when a timeout occurs

The execution stops immediately, without executing the CATCH block. Listing 1-18 demonstrates that the connection is still in the middle of an outstanding transaction.

Listing 1-18: The connection is in the middle of an outstanding transaction

If the client initiates a timeout, the behavior is exactly the same: the execution stops immediately, the outstanding transaction is neither committed nor rolled back, and an unhandled exception is sent to the client. This is simply how timeouts work and the only way to avoid this behavior is to turn it off altogether. For instance, we can turn off timeouts in ADO.NET by setting the CommandTimeout property to 0.  Of course, we can turn XACT_ABORT on, in which case at least the transaction will be rolled back. The CATCH block, however, will still be bypassed.

Problems with TRY…CATCH Scope

In some cases, the behavior is TRY…CATCH is documented, but will be surprising to developers used to error handling in languages such as C#.

Listing 1-19 demonstrates a simple case of a query, wrapped in a TRY…CATCH, which tries to use a temporary table that does not exist. However, the CATCH block is not executed, and we get an unhandled exception.

Listing 1-19: Sometimes a CATCH block is bypassed when an error occurs

Even more surprising for object-oriented developers is that this is not a bug; it is the just the way SQL Server works in this case. According to MSDN for SQL Server 2008,

“Errors that occur during statement-level recompilation…are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct”.

The issue here is that compilation errors that occur at run-time (as a result of deferred name resolution) abort the rest of the scope, which is equal to the batch in directly submitted SQL, but only equal to the rest of the procedure in a stored procedure or function. So a TRY…CATCH at the same scope will not intercept these errors, but a TRY…CATCH on a different scope (regardless of being nested or not) will catch it.

My point here is simple: SQL Server does not always handle errors in a way object oriented languages do. If we choose to use the error handling provided by SQL Server, we really need to learn it in detail or we will be in for some unpleasant surprises.

Doomed Transactions

There is another serious problem with T-SQL TRY…CATCH blocks: in some cases an error that occurred inside a TRY block is considered so severe that the whole transaction is doomed, or, in other words, it cannot be committed. Theoretically, the concept of doomed transactions makes perfect sense. Unfortunately, some really trivial errors, such as conversion errors, render transactions doomed if we use TRY…CATCH provided by T-SQL. For example, consider the transactions shown in Listing 1-20. The first attempts to perform a 1/0 calculation and the second to convert a strong to an integer. We do not want to roll back the whole transaction if an error occurs, so we set XACT_ABORT to OFF.

Listing 1-20: A transaction is doomed after a trivial error such as a conversion error.

As the output demonstrates, we can commit a transaction after a divide by zero, but a conversion error renders the transaction doomed, and therefore un-commitable. The latter case demonstrates that even a seemingly-trivial conversion error considered severe enough to override the XACT_ABORT setting, and the whole transaction is automatically rolled back.

To determine whether or not our transaction is committable, within TRY…CATCH, we can use the XACT_STATE() function, as demonstrated in listing 1-21.

Listing 1-21: Using xact_state to determine if our transaction is committable or doomed

Clearly there are situations where the concept of a doomed transaction makes sense. For example, if the server runs out of disk space while running a transaction then there is no way the transaction could complete. Unfortunately, the current implementation of SQL Server sometimes dooms transactions for very trivial reasons. In all-too-many cases this peculiar behavior of SQL Server makes it impossible to develop feature rich error handling in T-SQL, because if a transaction is doomed, we have no choice other than to roll it back.

We will not cover any examples here, but this can also cause problems when attempting to use SAVEPOINTs. Consider the following, very common, requirement:

“If our stored procedure is invoked in the middle of an outstanding transaction, and if any command in our stored procedure fails, undo only the changes made by the stored procedure. Do not make any decisions regarding the changes done outside of our stored procedure”.

Unfortunately, there is no robust way to implement such requirements in T-SQL using a SAVEPOINT. While it will work in most cases, it will not work as intended when a transaction is doomed.

Client-side Error Handling

In order to overcome the described limitations and difficulties with error handling using SQL Server’s TRY…CATCH, my advice is simple: when we need to implement feature-rich error handling, to respond intelligently to an anticipated error, we should do it in a language that offers more robust error handling, such as C#.

By doing so, we avoid complications caused by doomed transactions (for example, trivial conversion errors in a C# TRY block will never doom a transaction), or by error numbers being changed when they are re-thrown, and so on. Furthermore, once error handling is implemented in a C# class it can be re-used by all modules that need it, so we promote code reuse to its fullest extent.

Nowadays many of us developers use more than one language in our daily activities, and the reason is very simple and very pragmatic: in many cases it is much easier to learn a new language to accomplish a specific task, to which the language is well-suited, than it is to try to ‘bend’ a single language to all purposes.

By way of an example, Listing 1-22 re-implements in C# our “retry after deadlock” logic, from Listing 1-8. We need only implement this logic once, and we can use this class to execute any command against SQL Server.

Listing 1-22. Implementing the “retry after deadlock” logic in a C# class.

Let’s try this class out. First of all, we need to remove the retry logic from our ChangeCodeDescription stored procedure, but keep it just as prone to deadlocks as before. Listing 1-23 shows how to accomplish that.

Listing 1-23: Removing the retry logic from the ChangeCodeDescription stored procedure.

Obviously we’d first need to test this procedure and verify that it can successfully complete; a step that I will leave as a simple exercise.

In order to test what happens when we have a deadlock, we need to first reset our test data by rerunning script 1-9. Next, start a SERIALIZABLE transaction against the CodeDescriptionsChangeLog table, by running the script in Listing 1-10.

Rather than invoke our ChangeCodeDescription stored procedure forma  second SSMS session, as before, we need to execute the C# code shown in Listing 1-24, which invokes the same stored procedure through our RetryAfterDeadlock method.

Listing 1-24: Using the RetryAfterDeadlock method to invoke our stored procedure.

This method will not complete, as the table is locked by our SSMS transaction. Return to SSMS and highlight and execute the commented code, both the UPDATE command and the COMMIT. The transaction invoked from C# will be chosen as a deadlock victim and it will retry, and there is enough debugging output in our C# code to demonstrate what is happening.

Finally, let us verify that, after the retry, the modification completed, as shown in Listing 1-25.

Listing 1-25: Checking that the data is in the expected state.

In short, C# allows us to implement our “retry after deadlock” logic just once and reuse it as many times as we need. As defensive programmers, we really want to reuse our code, not to cut and paste the same code all over our systems and so we have a strong motivation to use a good modern tool such as C# for our error handling.

My message here is quite moderate. I am not suggesting that we abandon T-SQL error handling; far from it. In the simplest cases, when all we need is to roll back and raise an error, we should use XACT_ABORT and transactions. Notice that in Listing 1-23, we use XACT_ABORT and a transaction to roll back after a deadlock, but we implement all of the more complex error handling logic in C#.

Of course, there are situations when we do need to implement error handling in T-SQL. Whenever we are considering such an option, we need to realize that error handling in T-SQL is very complex and not really intuitive to a developer with experience in other languages. Also, it has a lot of gotchas, and it lacks some features which client side programmers consider as their birthright, such as the ability to re-throw an error exactly as it was caught.

Conclusion

It is essential that the defensive database programmer includes robust error handling in all production T-SQL code. However, as much as the introduction of TRY…CATCH has improved error handling in T-SQL, it still lacks the versatility, elegance and ease of use that is typical of client-side languages such as Java and C#. Ultimately, you will find that it is not possible to handle certain errors in Transact SQL at all and that we need to complement our T-SQL error handling with error handling on the client.

I hope this article has taught you the following specific lessons in defensive error handling:

• If you already use a modern language such as C# in your system, then it makes sense to utilize it to do complex handling of errors related to the database
• If handling errors on SQL Server, keep it simple where possible; set XACT_ABORT to ON and use transactions in order to roll back and raise an error
• If you wish to use TRY…CATCH, learn it thoroughly, and watch out in particular for the following problems:
  • One and the same code may run differently depending on XACT_ABORT setting
  • We cannot re-throw errors exactly as we catch them
  • CATCH blocks do not catch all errors
  • Some errors do not respect XACT_ABORT settings
  • Some transaction may be rendered un-committable, a.k.a. doomed

This article is an extract from Alex’s book ‘Defensive Database Programming’ available from Amazon now.

Привет! Представляю вашему вниманию перевод статьи «Error and Transaction Handling in SQL Server. Part One – Jumpstart Error Handling» автора Erland Sommarskog.

1. Введение

Эта статья – первая в серии из трёх статей, посвященных обработке ошибок и транзакций в SQL Server. Её цель – дать вам быстрый старт в теме обработки ошибок, показав базовый пример, который подходит для большей части вашего кода. Эта часть написана в расчете на неопытного читателя, и по этой причине я намеренно умалчиваю о многих деталях. В данный момент задача состоит в том, чтобы рассказать как без упора на почему. Если вы принимаете мои слова на веру, вы можете прочесть только эту часть и отложить остальные две для дальнейших этапов в вашей карьере.

С другой стороны, если вы ставите под сомнение мои рекомендации, вам определенно необходимо прочитать две остальные части, где я погружаюсь в детали намного более глубоко, исследуя очень запутанный мир обработки ошибок и транзакций в SQL Server. Вторая и третья части, так же, как и три приложения, предназначены для читателей с более глубоким опытом. Первая статья — короткая, вторая и третья значительно длиннее.

Все статьи описывают обработку ошибок и транзакций в SQL Server для версии 2005 и более поздних версий.

1.1 Зачем нужна обработка ошибок?

Почему мы обрабатываем ошибки в нашем коде? На это есть много причин. Например, на формах в приложении мы проверяем введенные данные и информируем пользователей о допущенных при вводе ошибках. Ошибки пользователя – это предвиденные ошибки. Но нам также нужно обрабатывать непредвиденные ошибки. То есть, ошибки могут возникнуть из-за того, что мы что-то упустили при написании кода. Простой подход – это прервать выполнение или хотя бы вернуться на этап, в котором мы имеем полный контроль над происходящим. Недостаточно будет просто подчеркнуть, что совершенно непозволительно игнорировать непредвиденные ошибки. Это недостаток, который может вызвать губительные последствия: например, стать причиной того, что приложение будет предоставлять некорректную информацию пользователю или, что еще хуже, сохранять некорректные данные в базе. Также важно сообщать о возникновении ошибки с той целью, чтобы пользователь не думал о том, что операция прошла успешно, в то время как ваш код на самом деле ничего не выполнил.

Мы часто хотим, чтобы в базе данных изменения были атомарными. Например, задача по переводу денег с одного счета на другой. С этой целью мы должны изменить две записи в таблице CashHoldings и добавить две записи в таблицу Transactions. Абсолютно недопустимо, чтобы ошибки или сбой привели к тому, что деньги будут переведены на счет получателя, а со счета отправителя они не будут списаны. По этой причине обработка ошибок также касается и обработки транзакций. В приведенном примере нам нужно обернуть операцию в BEGIN TRANSACTION и COMMIT TRANSACTION, но не только это: в случае ошибки мы должны убедиться, что транзакция откачена.

2. Основные команды

Мы начнем с обзора наиболее важных команд, которые необходимы для обработки ошибок. Во второй части я опишу все команды, относящиеся к обработке ошибок и транзакций.

2.1 TRY-CATCH

Основным механизмом обработки ошибок является конструкция TRY-CATCH, очень напоминающая подобные конструкции в других языках. Структура такова:

BEGIN TRY
   <обычный код>
END TRY
BEGIN CATCH
   <обработка ошибок>
END CATCH

Если какая-либо ошибка появится в <обычный код>, выполнение будет переведено в блок CATCH, и будет выполнен код обработки ошибок.

Как правило, в CATCH откатывают любую открытую транзакцию и повторно вызывают ошибку. Таким образом, вызывающая клиентская программа понимает, что что-то пошло не так. Повторный вызов ошибки мы обсудим позже в этой статье.

Вот очень быстрый пример:

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

Результат выполнения: This is the error: Divide by zero error encountered.

Мы вернемся к функции error_message() позднее. Стоит отметить, что использование PRINT в обработчике CATCH приводится только в рамках экспериментов и не следует делать так в коде реального приложения.

Если <обычный код> вызывает хранимую процедуру или запускает триггеры, то любая ошибка, которая в них возникнет, передаст выполнение в блок CATCH. Если более точно, то, когда возникает ошибка, SQL Server раскручивает стек до тех пор, пока не найдёт обработчик CATCH. И если такого обработчика нет, SQL Server отправляет сообщение об ошибке напрямую клиенту.

Есть одно очень важное ограничение у конструкции TRY-CATCH, которое нужно знать: она не ловит ошибки компиляции, которые возникают в той же области видимости. Рассмотрим пример:

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

Выходные данные:

This prints
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
Invalid object name 'NoSuchTable'

Как можно видеть, блок TRY присутствует, но при возникновении ошибки выполнение не передается блоку CATCH, как это ожидалось. Это применимо ко всем ошибкам компиляции, таким как пропуск колонок, некорректные псевдонимы и тому подобное, которые возникают во время выполнения. (Ошибки компиляции могут возникнуть в SQL Server во время выполнения из-за отложенного разрешения имен – особенность, благодаря которой SQL Server позволяет создать процедуру, которая обращается к несуществующим таблицам.)

Эти ошибки не являются полностью неуловимыми; вы не можете поймать их в области, в которой они возникают, но вы можете поймать их во внешней области. Добавим такой код к предыдущему примеру:

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

Теперь мы получим на выходе это:

This prints
The error message is: Invalid object name 'NoSuchTable'.

На этот раз ошибка была перехвачена, потому что сработал внешний обработчик CATCH.

2.2 SET XACT_ABORT ON

В начало ваших хранимых процедур следует всегда добавлять это выражение:

SET XACT_ABORT, NOCOUNT ON

Оно активирует два параметра сессии, которые выключены по умолчанию в целях совместимости с предыдущими версиями, но опыт доказывает, что лучший подход – это иметь эти параметры всегда включенными. Поведение SQL Server по умолчанию в той ситуации, когда не используется TRY-CATCH, заключается в том, что некоторые ошибки прерывают выполнение и откатывают любые открытые транзакции, в то время как с другими ошибками выполнение последующих инструкций продолжается. Когда вы включаете XACT_ABORT ON, почти все ошибки начинают вызывать одинаковый эффект: любая открытая транзакция откатывается, и выполнение кода прерывается. Есть несколько исключений, среди которых наиболее заметным является выражение RAISERROR.

Параметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката.

Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.

Параметр NOCOUNT не имеет к обработке ошибок никакого отношения, но включение его в код является хорошей практикой. NOCOUNT подавляет сообщения вида (1 row(s) affected), которые вы можете видеть в панели Message в SQL Server Management Studio. В то время как эти сообщения могут быть полезны при работе c SSMS, они могут негативно повлиять на производительность в приложении, так как увеличивают сетевой трафик. Сообщение о количестве строк также может привести к ошибке в плохо написанных клиентских приложениях, которые могут подумать, что это данные, которые вернул запрос.

Выше я использовал синтаксис, который немного необычен. Большинство людей написали бы два отдельных выражения:

SET NOCOUNT ON
SET XACT_ABORT ON

Между ними нет никакого отличия. Я предпочитаю версию с SET и запятой, т.к. это снижает уровень шума в коде. Поскольку эти выражения должны появляться во всех ваших хранимых процедурах, они должны занимать как можно меньше места.

3. Основной пример обработки ошибок

После того, как мы посмотрели на TRY-CATCH и SET XACT_ABORT ON, давайте соединим их вместе в примере, который мы можем использовать во всех наших хранимых процедурах. Для начала я покажу пример, в котором ошибка генерируется в простой форме, а в следующем разделе я рассмотрю решения получше.

Для примера я буду использовать эту простую таблицу.

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

Вот хранимая процедура, которая демонстрирует, как вы должны работать с ошибками и транзакциями.

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

Первая строка в процедуре включает XACT_ABORT и NOCOUNT в одном выражении, как я показывал выше. Эта строка – единственная перед BEGIN TRY. Все остальное в процедуре должно располагаться после BEGIN TRY: объявление переменных, создание временных таблиц, табличных переменных, всё. Даже если у вас есть другие SET-команды в процедуре (хотя причины для этого встречаются редко), они должны идти после BEGIN TRY.

Причина, по которой я предпочитаю указывать SET XACT_ABORT и NOCOUNT перед BEGIN TRY, заключается в том, что я рассматриваю это как одну строку шума: она всегда должна быть там, но я не хочу, чтобы это мешало взгляду. Конечно же, это дело вкуса, и если вы предпочитаете ставить SET-команды после BEGIN TRY, ничего страшного. Важно то, что вам не следует ставить что-либо другое перед BEGIN TRY.

Часть между BEGIN TRY и END TRY является основной составляющей процедуры. Поскольку я хотел использовать транзакцию, определенную пользователем, я ввел довольно надуманное бизнес-правило, в котором говорится, что если вы вставляете пару, то обратная пара также должна быть вставлена. Два выражения INSERT находятся внутри BEGIN и COMMIT TRANSACTION. Во многих случаях у вас будет много строк кода между BEGIN TRY и BEGIN TRANSACTION. Иногда у вас также будет код между COMMIT TRANSACTION и END TRY, хотя обычно это только финальный SELECT, возвращающий данные или присваивающий значения выходным параметрам. Если ваша процедура не выполняет каких-либо изменений или имеет только одно выражение INSERT/UPDATE/DELETE/MERGE, то обычно вам вообще не нужно явно указывать транзакцию.

В то время как блок TRY будет выглядеть по-разному от процедуры к процедуре, блок CATCH должен быть более или менее результатом копирования и вставки. То есть вы делаете что-то короткое и простое и затем используете повсюду, не особо задумываясь. Обработчик CATCH, приведенный выше, выполняет три действия:

1. Откатывает любые открытые транзакции.
2. Повторно вызывает ошибку.
3. Убеждается, что возвращаемое процедурой значение отлично от нуля.

Эти три действия должны всегда быть там. Мы можете возразить, что строка

IF @@trancount > 0 ROLLBACK TRANSACTION

не нужна, если нет явной транзакции в процедуре, но это абсолютно неверно. Возможно, вы вызываете хранимую процедуру, которая открывает транзакцию, но которая не может ее откатить из-за ограничений TRY-CATCH. Возможно, вы или кто-то другой добавите явную транзакцию через два года. Вспомните ли вы тогда о том, что нужно добавить строку с откатом? Не рассчитывайте на это. Я также слышу читателей, которые возражают, что если тот, кто вызывает процедуру, открыл транзакцию, мы не должны ее откатывать… Нет, мы должны, и если вы хотите знать почему, вам нужно прочитать вторую и третью части. Откат транзакции в обработчике CATCH – это категорический императив, у которого нет исключений.

Код повторной генерации ошибки включает такую строку:

DECLARE @msg nvarchar(2048) = error_message()

Встроенная функция error_message() возвращает текст возникшей ошибки. В следующей строке ошибка повторно вызывается с помощью выражения RAISERROR. Это не самый простой способ вызова ошибки, но он работает. Другие способы мы рассмотрим в следующей главе.

Замечание: синтаксис для присвоения начального значения переменной в DECLARE был внедрен в SQL Server 2008. Если у вас SQL Server 2005, вам нужно разбить строку на DECLARE и выражение SELECT.

Финальное выражение RETURN – это страховка. RAISERROR никогда не прерывает выполнение, поэтому выполнение следующего выражения будет продолжено. Пока все процедуры используют TRY-CATCH, а также весь клиентский код обрабатывает исключения, нет повода для беспокойства. Но ваша процедура может быть вызвана из старого кода, написанного до SQL Server 2005 и до внедрения TRY-CATCH. В те времена лучшее, что мы могли делать, это смотреть на возвращаемые значения. То, что вы возвращаете с помощью RETURN, не имеет особого значения, если это не нулевое значение (ноль обычно обозначает успешное завершение работы).

Последнее выражение в процедуре – это END CATCH. Никогда не следует помещать какой-либо код после END CATCH. Кто-нибудь, читающий процедуру, может не увидеть этот кусок кода.

После прочтения теории давайте попробуем тестовый пример:

EXEC insert_data 9, NULL

Результат выполнения:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.

Давайте добавим внешнюю процедуру для того, чтобы увидеть, что происходит при повторном вызове ошибки:

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

Результат работы:

Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Мы получили корректное сообщение об ошибке, но если вы посмотрите на заголовки этого сообщения и на предыдущее поближе, то можете заметить проблему:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9

Сообщение об ошибке выводит информацию о расположении конечного выражения RAISERROR. В первом случае некорректен только номер строки. Во втором случае некорректно также имя процедуры. Для простых процедур, таких как наш тестовый пример, это не является большой проблемой. Но если у вас есть несколько уровней вложенных сложных процедур, то наличие сообщения об ошибке с отсутствием указания на место её возникновения сделает поиск и устранение ошибки намного более сложным делом. По этой причине желательно генерировать ошибку таким образом, чтобы можно было определить нахождение ошибочного фрагмента кода быстро, и это то, что мы рассмотрим в следующей главе.

4. Три способа генерации ошибки

4.1 Использование error_handler_sp

Мы рассмотрели функцию error_message(), которая возвращает текст сообщения об ошибке. Сообщение об ошибке состоит из нескольких компонентов, и существует своя функция error_xxx() для каждого из них. Мы можем использовать их для повторной генерации полного сообщения, которое содержит оригинальную информацию, хотя и в другом формате. Если делать это в каждом обработчике CATCH, это будет большой недостаток — дублирование кода. Вам не обязательно находиться в блоке CATCH для вызова error_message() и других подобных функций, и они вернут ту же самую информацию, если будут вызваны из хранимой процедуры, которую выполнит блок CATCH.

Позвольте представить вам 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)

Первое из того, что делает error_handler_sp – это сохраняет значение всех error_xxx() функций в локальные переменные. Я вернусь к выражению IF через секунду. Вместо него давайте посмотрим на выражение SELECT внутри IF:

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

Цель этого SELECT заключается в форматировании сообщения об ошибке, которое передается в RAISERROR. Оно включает в себя всю информацию из оригинального сообщения об ошибке, которое мы не можем вставить напрямую в RAISERROR. Мы должны обработать имя процедуры, которое может быть NULL для ошибок в обычных скриптах или в динамическом SQL. Поэтому используется функция COALESCE. (Если вы не понимаете форму выражения RAISERROR, я рассказываю о нем более детально во второй части.)

Отформатированное сообщение об ошибке начинается с трех звездочек. Этим достигаются две цели: 1) Мы можем сразу видеть, что это сообщение вызвано из обработчика CATCH. 2) Это дает возможность для error_handler_sp отфильтровать ошибки, которые уже были сгенерированы один или более раз, с помощью условия NOT LIKE ‘***%’ для того, чтобы избежать изменения сообщения во второй раз.

Вот как обработчик CATCH должен выглядеть, когда вы используете error_handler_sp:

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

Давайте попробуем несколько тестовых сценариев.

EXEC insert_data 8, NULL
EXEC outer_sp 8, 8

Результат выполнения:

Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20
*** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20
*** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Заголовки сообщений говорят о том, что ошибка возникла в процедуре error_handler_sp, но текст сообщений об ошибках дает нам настоящее местонахождение ошибки – как название процедуры, так и номер строки.

Я покажу еще два метода вызова ошибок. Однако error_handler_sp является моей главной рекомендацией для читателей, которые читают эту часть. Это — простой вариант, который работает на всех версиях SQL Server начиная с 2005. Существует только один недостаток: в некоторых случаях SQL Server генерирует два сообщения об ошибках, но функции error_xxx() возвращают только одну из них, и поэтому одно из сообщений теряется. Это может быть неудобно при работе с административными командами наподобие BACKUPRESTORE, но проблема редко возникает в коде, предназначенном чисто для приложений.

4.2. Использование ;THROW

В SQL Server 2012 Microsoft представил выражение ;THROW для более легкой обработки ошибок. К сожалению, Microsoft сделал серьезную ошибку при проектировании этой команды и создал опасную ловушку.

С выражением ;THROW вам не нужно никаких хранимых процедур. Ваш обработчик CATCH становится таким же простым, как этот:

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

Достоинство ;THROW в том, что сообщение об ошибке генерируется точно таким же, как и оригинальное сообщение. Если изначально было два сообщения об ошибках, оба сообщения воспроизводятся, что делает это выражение еще привлекательнее. Как и со всеми другими сообщениями об ошибках, ошибки, сгенерированные ;THROW, могут быть перехвачены внешним обработчиком CATCH и воспроизведены. Если обработчика CATCH нет, выполнение прерывается, поэтому оператор RETURN в данном случае оказывается не нужным. (Я все еще рекомендую оставлять его, на случай, если вы измените свое отношение к ;THROW позже).

Если у вас SQL Server 2012 или более поздняя версия, измените определение insert_data и outer_sp и попробуйте выполнить тесты еще раз. Результат в этот раз будет такой:

Msg 515, Level 16, State 2, Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 2627, Level 14, State 1, Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Имя процедуры и номер строки верны и нет никакого другого имени процедуры, которое может нас запутать. Также сохранены оригинальные номера ошибок.

В этом месте вы можете сказать себе: действительно ли Microsoft назвал команду ;THROW? Разве это не просто THROW? На самом деле, если вы посмотрите в Books Online, там не будет точки с запятой. Но точка с запятой должны быть. Официально они отделяют предыдущее выражение, но это опционально, и далеко не все используют точку с запятой в выражениях T-SQL. Более важно, что если вы пропустите точку с запятой перед THROW, то не будет никакой синтаксической ошибки. Но это повлияет на поведение при выполнении выражения, и это поведение будет непостижимым для непосвященных. При наличии активной транзакции вы получите сообщение об ошибке, которое будет полностью отличаться от оригинального. И еще хуже, что при отсутствии активной транзакции ошибка будет тихо выведена без обработки. Такая вещь, как пропуск точки с запятой, не должно иметь таких абсурдных последствий. Для уменьшения риска такого поведения, всегда думайте о команде как о ;THROW (с точкой с запятой).

Нельзя отрицать того, что ;THROW имеет свои преимущества, но точка с запятой не единственная ловушка этой команды. Если вы хотите использовать ее, я призываю вас прочитать по крайней мере вторую часть этой серии, где я раскрываю больше деталей о команде ;THROW. До этого момента, используйте error_handler_sp.

4.3. Использование SqlEventLog

Третий способ обработки ошибок – это использование SqlEventLog, который я описываю очень детально в третьей части. Здесь я лишь сделаю короткий обзор.

SqlEventLog предоставляет хранимую процедуру slog.catchhandler_sp, которая работает так же, как и error_handler_sp: она использует функции error_xxx() для сбора информации и выводит сообщение об ошибке, сохраняя всю информацию о ней. Вдобавок к этому, она логирует ошибку в таблицу splog.sqleventlog. В зависимости от типа приложения, которое у вас есть, эта таблица может быть очень ценным объектом.

Для использования SqlEventLog, ваш обработчик CATCH должен быть таким:

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

@@procid возвращает идентификатор объекта текущей хранимой процедуры. Это то, что SqlEventLog использует для логирования информации в таблицу. Используя те же тестовые сценарии, получим результат их работы с использованием catchhandler_sp:

Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125
{515} Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125
{2627} Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Как вы видите, сообщение об ошибке отформатировано немного не так, как это делает error_handler_sp, но основная идея такая же. Вот образец того, что было записано в таблицу slog.sqleventlog:

Если вы хотите попробовать SqlEventLog, вы можете загрузить файл sqleventlog.zip. Инструкция по установке находится в третьей части, раздел Установка SqlEventLog.

5. Финальные замечания

Вы изучили основной образец для обработки ошибок и транзакций в хранимых процедурах. Он не идеален, но он должен работать в 90-95% вашего кода. Есть несколько ограничений, на которые стоит обратить внимание:

1. Как мы видели, ошибки компиляции не могут быть перехвачены в той же процедуре, в которой они возникли, а только во внешней процедуре.
2. Пример не работает с пользовательскими функциями, так как ни TRY-CATCH, ни RAISERROR нельзя в них использовать.
3. Когда хранимая процедура на Linked Server вызывает ошибку, эта ошибка может миновать обработчик в хранимой процедуре на локальном сервере и отправиться напрямую клиенту.
4. Когда процедура вызвана как INSERT-EXEC, вы получите неприятную ошибку, потому что ROLLBACK TRANSACTION не допускается в данном случае.
5. Как упомянуто выше, если вы используете error_handler_sp или SqlEventLog, мы потеряете одно сообщение, когда SQL Server выдаст два сообщения для одной ошибки. При использовании ;THROW такой проблемы нет.

Я рассказываю об этих ситуациях более подробно в других статьях этой серии.

Перед тем как закончить, я хочу кратко коснуться триггеров и клиентского кода.

Триггеры

Пример для обработки ошибок в триггерах не сильно отличается от того, что используется в хранимых процедурах, за исключением одной маленькой детали: вы не должны использовать выражение RETURN (потому что RETURN не допускается использовать в триггерах).

С триггерами важно понимать, что они являются частью команды, которая запустила триггер, и в триггере вы находитесь внутри транзакции, даже если не используете BEGIN TRANSACTION.
Иногда я вижу на форумах людей, которые спрашивают, могут ли они написать триггер, который не откатывает в случае падения запустившую его команду. Ответ таков: нет способа сделать это надежно, поэтому не стоит даже пытаться. Если в этом есть необходимость, по возможности не следует использовать триггер вообще, а найти другое решение. Во второй и третьей частях я рассматриваю обработку ошибок в триггерах более подробно.

Клиентский код

У вас должна быть обработка ошибок в коде клиента, если он имеет доступ к базе. То есть вы должны всегда предполагать, что при любом вызове что-то может пойти не так. Как именно внедрить обработку ошибок, зависит от конкретной среды.

Здесь я только обращу внимание на важную вещь: реакцией на ошибку, возвращенную SQL Server, должно быть завершение запроса во избежание открытых бесхозных транзакций:

IF @@trancount > 0 ROLLBACK TRANSACTION

Это также применимо к знаменитому сообщению Timeout expired (которое является не сообщением от SQL Server, а от API).

6. Конец первой части

Это конец первой из трех частей серии. Если вы хотели изучить вопрос обработки ошибок быстро, вы можете закончить чтение здесь. Если вы настроены идти дальше, вам следует прочитать вторую часть, где наше путешествие по запутанным джунглям обработки ошибок и транзакций в SQL Server начинается по-настоящему.

… и не забывайте добавлять эту строку в начало ваших хранимых процедур:

SET XACT_ABORT, NOCOUNT ON

Автор: Schekotka

Источник

As a sql developer, we must learn how to handle error in sql code and log them properly so other developer can fix them quickly.

In real-time SQL programming often we need to catch application error to find the root cause,
like any other programming language, here we learn the best way to deal with error and catch exception in sql server database.

Begin Try 

End Try
Begin Catch

End Catch

SQL Error handling helps us getting control over Transact-SQL code, as a developer whenever we write any SQL code that runs based on input, if input data is not as expected that may cause error in code, so we should implement error handing whenever we write any sql server stored procedure or triggers, cursor etc, error can happen while inserting, or updating data, also sometimes at the time of deleting data if there is foreign key reference

So, let’s look at how to implement sql server error handling with try, catch , throw, raiserror

Like any other programming language there is a technique of error handling in SQL Server
query during database development.

SQL Server has two types of exceptions:

• System Defined
• User Defined

Here are few built-in functions that can help us getting error details,
We can use all those function together and capture error of any DML statement

• ERROR_NUMBER

  This will tell us about internal number of the error

• ERROR_STATE

  Returns the information about the source

• ERROR_SEVERITY
• ERROR_LINE

  tell you where the error is, very helpful

• ERROR_PROCEDURE

  Returns the name of the stored procedure or function

• ERROR_MESSAGE

  this will get the error details

Here is how Sql insert error handling can be done,
also see how Sql error message displayed using error_message()

Note:
In SQL Server RAISERROR and THROW both statements are used to raise an error. RAISERROR was introduced from SQL Server 7.0, where as the journey of THROW statement has just began with SQL Server 2012

RAISERROR eventually will be replaced by THROW

Now we can use RAISERROR inside catch block like

RAISERROR(ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE())

Here is a simple example how we can get all error details as a row with the help of try catch block, this way we can implement error handling in any stored procedure, trigger, cursor etc.

BEGIN TRY
    /* Generate a divide-by-zero error  , you can write your insert or update statment */ 
	SELECT	(0/0) AS Error;
END TRY
BEGIN CATCH
	SELECT
		ERROR_NUMBER() AS ErrorNumber,
		ERROR_STATE() AS ErrorState,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_PROCEDURE() AS ErrorProcedure,
		ERROR_LINE() AS ErrorLine,
		ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

The best way to improve your programme would be capturing all sql errors and store them in a SQL table, that will help your developers to understand the error message and the source of error, so they can analyse and resolve the error.

Create a simple table in your database with following SQL code

CREATE TABLE tbErrors
		(ErrorID        INT IDENTITY(1, 1),
		UserName       VARCHAR(100),
		ErrorNumber    INT,
		ErrorState     INT,
		ErrorSeverity  INT,
		ErrorLine      INT,
		ErrorProcedure VARCHAR(MAX),
		ErrorMessage   VARCHAR(MAX),
		ErrorDateTime  DATETIME)
GO

Now everywhere inside catch statement write an insert statement that will insert all error details in the above table, so it will be easy to diagnosis and understand the root cause of the error

SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log file location'

We also can get error log information by executing SP_READERRORLOG, but to execute this
sp user need to have securityadmin role in that sql sever.

Now so far have seen how to implement error handling in sql code, now we see when it become crucial to handle error in updating or not updating data in other table.

Think of situation like when we need to update or insert data into multiple tables; and we want either all data will get inserted or none, we don’t want partial insertion.

To handle such situation we use transaction, in following example i am trying to update two tables
«tbOrderItem» and «tbStock»,
If anything goes wrong, then no table will be updated and we catch the exact error details.

      
BEGIN TRY  
    BEGIN TRANSACTION;  
    
	UPDATE [dbo].[tbOrderItem]
	   SET [orderId] = 1, [productId] =1, 
		   [quantity] = 120,[unitPrice] = 10.25
	 WHERE oitemId =2
	 	 
	 UPDATE [dbo].[tbStock]
	   SET [quantity] = 5 ,[price] = 10.25,      
		   [updatedOn] = getdate()
	  WHERE StockId=1
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
	 ROLLBACK TRANSACTION;  
	 SELECT  
        ERROR_NUMBER() AS ErrorNumber,  
        ERROR_SEVERITY() AS ErrorSeverity,  
        ERROR_STATE() AS ErrorState,  
        ERROR_PROCEDURE() AS ErrorProcedure,  
        ERROR_LINE() AS ErrorLine,  
        ERROR_MESSAGE() AS ErrorMessage;    
END CATCH;  
GO  

This was an example of how you can use error handling with transaction.

You also may be interested in following tutorial

• SQL Server Transaction Example
• Improve SQL Query Performance in MS SQL Server Database

This is the first article in the series of articles on Exception Handling in Sql Server. Below is the complete list of articles in this series.

Part   I: Exception Handling Basics
Part  II: TRY…CATCH (Introduced in Sql Server 2005)
Part III: RAISERROR Vs THROW (Throw: Introduced in Sql Server 2012)
Part IV: Exception Handling Template

If we are not clear about the basics of Exception handling in Sql Server, then it is the most complex/confusing task, it will become nightmarish job to identify the actual error and the root cause of the unexpected results. In this blog post I will try to make sure that all the concepts are cleared properly and the one who goes through it should feel the sense of something they have learnt new and feel themselves an expert in this area. And at the end of the blog post will present the ideal exception handling template which one should be using for proper error handling in Sql Server.

Last week on 11th January, 2014, I have presented a session on this topic at Microsoft Office in the Sql Bangalore User Group meeting which is attend by hundreds of enthusiastic Sql Server working professionals. Received very good feedback and few messages posted in the Facebook SQLBangalore user group were “Thanks Basavaraj Biradar! Your session was divine!” By Community Member Adarsh Prasad, “Thanks Basavaraj for your excellent session” By Community Member Selva Raj. Enough Self Praise, enough expectation is set, let’s cut short the long story short and move onto the deep dive of Exception handling basics

In this topic will cover the following concepts with extensive list of examples

• Error Message
• Error Actions

Let’s start with a simple statement like below which results in an exception as I am trying to access a non-existing table.

--------------Try To Access Non-Existing Table ---------------
 SELECT * FROM dbo.NonExistingTable
 GO

Result of the above query:
Msg 208, Level 16, State 1, Line 2
Invalid object name ‘dbo.NonExistingTable’.

By looking at the above error message, we can see that the error message consists of following 5 parts:

Msg 208 – Error Number
Level 16 – Severity of the Error
State 1 – State of the Error
Line 2 – Line Number of the statement which generated the Error
Invalid object name ‘dbo.NonExistingTable’. – Actual Error Message

Now Let’s wrap the above statement which generated the error into a stored procedure as below

---Create the Stored Procedure 
CREATE PROCEDURE dbo.ErrorMessageDemo
AS 
BEGIN
	SELECT * FROM dbo.NonExistingTable
END
GO
--Execute the Stored Procedure
EXEC dbo.ErrorMessageDemo
GO

Result of executing the above stored procedure is:
Msg 208, Level 16, State 1, Procedure ErrorMessageDemo, Line 4
Invalid object name ‘dbo.NonExistingTable’.

If we compare this error message with the previous error message, then this message contains one extra part “Procedure ErrorMessageDemo“ specifying the name of the stored procedure in which the exception occurred.

Parts of ErrorMessage

The below image explains in detail each of the six parts of the error message which we have identified just above:

In case the image is not clear below is the detail which I have tried to present in it:

ERROR NUMBER/ Message Id:

Any error number which is <= 50000 is a System Defined Messages and the ones which are > 50000 are User Defined Messages. SYS.Messages catalog view can be used to retrieve both System and User Defined Messages. We can add a user defined message using sp_addmessage and we can remove it using the system stored procedure sp_dropmessage.

ERROR SEVERITY: Error Severity can be between 0-25.

0-10:  Informational or a warning
11-16: Programming Errors
17-25: Resource / Hardware / OS/ Sql Server Internal Errors
20-25: Terminates the Connection
19-25: Only User with SysAdmin rights can raise error’s with this severity

ERROR STATE: Same Error can be raised for several different conditions in the code. Each specific condition that raises the error assigns a unique state code. Also the SQL Support Team uses it to find the location in the source code where that error is being raised.

ERROR PROCEDURE: Name of the Stored Procedure or the Function in which the Error Occurred. It Will be blank if it is a Normal Batch of Statement.

ERROR LINE: Line Number of the Statement within SP/ UDF/ Batch which triggered the error. It will be 0 If SP/UDF Invoke Causes the Error.

ERROR MESSAGE: Error description detailing out the reason for the error

Now let us see how Sql Server Reacts to different errors. To demonstrate this let us create a New Database and table as shown below:

--Create a New database for the Demo
CREATE DATABASE SqlHintsErrorHandlingDemo
GO
USE SqlHintsErrorHandlingDemo
GO
CREATE TABLE dbo.Account
(	
 AccountId INT NOT NULL PRIMARY KEY, 
 Name	 NVARCHAR (50) NOT NULL,
 Balance Money NOT NULL CHECK (Balance>=0)	
)
GO

As the Account table has Primary Key on the AccountId column, so it will raise an error if we try to duplicate the AccountId column value. And the Balance column has a CHECK constraint Balance>=0, so it will raise an exception if the value of Balance is <0.

Let us first check whether we are able to insert valid Account into the Account table.

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1', 10000)

Result: We are able to successfully insert a record in the Account table

Now try to insert one more account whose AccountId is same as the one which we have just inserted above.

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Duplicate', 10000)

Result: It fails with below error message, because we are trying to insert a duplicate value for the the Primary Key column AccountId.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK__Account__349DA5A67ED5FC72’. Cannot insert duplicate key in object ‘dbo.Account’. The duplicate key value is (1).
The statement has been terminated.

Let me empty the Account Table by using the below statement:

DELETE FROM dbo.Account

DEMO 1: Now let us see what will be the result if we execute the below batch of Statements:

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1', 10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Duplicate', 10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(2, 'Account2', 20000)
GO

Result: The First and Third Insert statements in the batch are succeeded even though the Second Insert statement fails

From the above example result it is clear that even though the Second insert statement is raising a primary key voilation error, Sql server continued the execution of the next statement and it has successfully inserted the Account with AccountId 2 by the third Insert statement.

If Sql Server terminates the statement which raised the error but continues to execute the next statements in the Batch. Then such a behavior by a Sql Server in response to an error is called Statement Termination.

Let me clear the Account Table by using the below statement before proceeding with the Next DEMO:

DELETE FROM dbo.Account

DEMO 2: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the DEMO 1 is the first line i.e. SET XACT_ABORT ON:

SET XACT_ABORT ON
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1',  10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Duplicate', 10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(2, 'Account2',  20000)
GO

RESULT: Only the first Insert succeeded

From the above example result it is clear that failure in the Second insert statement due to primary key violation caused Sql Server to terminate the execution of the Subsequent statements in the batch.

If Sql Server terminates the statement which raised the error and the subsequent statements in the batch then such behavior is termed as Batch Abortion.

The Only difference in the DEMO 2 script from DEMO 1 is the additional first statement SET XACT_ABORT ON. So from the result it is clear that the SET XACT_ABORT ON statement is causing Sql Server to do the Batch Abortion for a Statement Termination Error. It means SET XACT_ABORT ON converts the Statement Terminating errors to the Batch Abortion errors.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account 
SET XACT_ABORT OFF
GO

DEMO 3: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the DEMO 1 is that the INSERT statements are executed in a Transaction:

BEGIN TRAN
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(1, 'Account1',  10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(1, 'Duplicate', 10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(2, 'Account2',  20000)
COMMIT TRAN
GO

RESULT: Same as the DEMO 1, that is only the statement which raised the error is terminated but continues with the next statement in the batch. Here First and Third Inserts are Successful even though the Second statement raised the error.

Let me clear the Account Table by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account
GO

DEMO 4: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the DEMO 2 is that the INSERT statement’s are executed within a Transaction

SET XACT_ABORT ON
BEGIN TRAN
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(1, 'Account1',  10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(1, 'Duplicate', 10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(2, 'Account2',  20000)
COMMIT TRAN
GO

RESULT: No records inserted

From the above example result it is clear that SET XACT_ABORT ON setting not only converts the Statement Termination Errors to the Batch Abortion Errors and also ROLLS BACK any active transactions started prior to the BATCH Abortion errors.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account 
SET XACT_ABORT OFF
GO

DEMO 5: As a part of this DEMO we will verify what happens if a CONVERSION Error occurs within a batch of statement.

CONVERSION ERROR: Trying to convert the string ‘TEN THOUSAND’ to MONEY Type will result in an error. Let us see this with an example:

SELECT CAST('TEN THOUSAND' AS MONEY)

RESULT:
Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.

Now let us see what happens if we come across such a CONVERSION error within a batch of statement like the below one:

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1', 10000)

UPDATE dbo.Account 
SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) 
WHERE AccountId = 1

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(2, 'Account2',  20000)
GO

RESULT: Only the First INSERT is successful

From the above result it is clear that CONVERSION errors cause the BATCH abortion, i.e Sql Server terminates the statement which raised the error and the subsequent statements in the batch. Where as PRIMARY KEY violation was resulting in a Statement Termination as explained in the DEMO 1.

Let me clear the Account Table by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account
GO

DEMO 6: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the previous DEMO 5 is that the Batch statement’s are executed within a Transaction

BEGIN TRAN 
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(1, 'Account1', 10000)

 UPDATE dbo.Account 
 SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) 
 WHERE AccountId = 1

 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(2, 'Account2',  20000)
COMMIT TRAN 
GO

RESULT: No records inserted

From the above example result it is clear that CONVERSION errors results in a BATCH Abortion and BATCH Abortion errors ROLLS BACK any active transactions started prior to the BATCH Abortion error.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account 
SET XACT_ABORT OFF
GO

Enough examples, let me summarize the Sql Server Error Actions. Following are the four different ways Sql Server responds(i.e. Error Actions) in response to the errors:

• Statement Termination
• Scope Abortion
• Batch Abortion
• Connection Termination

Many of these error actions I have explained in the above DEMOs using multiple examples. To explain these error actions further let us take a scenario as shown in the below image, in this scenario from client system an Execution request for the MainSP is submitted and the MainSP internally calls to sub sp’s SubSP1 and SubSP2 one after another:

Statement Termination :

If Sql Server terminates the statement which raised the error but continues to execute the next statements in the Batch. Then such a behavior by a Sql Server in response to an error is called Statement Termination. As shown in the below image the Statement-1 in SubSP1 is causing an error, in response to this Sql Server terminates only the statement that raised the error i.e. Statement-1 but continues executing subsequent statements in the SubSP1 and MainSP calls the subsequent SP SubSp2.

Scope Abortion :

If Sql Server terminates the statement which raised the error and the subsequent statements in the same scope, but continues to execute all the Statements outside the scope of the statement which raised the error. As shown in the below image the Statement-1 in SubSP1 is causing an error, in response to this Sql Server terminates not only the statement that raised the error i.e. Statement-1, but also terminates all the subsequent statements in the SubSP1, but continues executing further all the statements/Sub Sp’s (For Example SubSP2) in the MainSP.

Let us see this behavior with stored procedures similar to the one explained in the above image. Let us execute the below script to create the three stored procedures for this demo:

-------------Scope Abortion Demo-------------
-------Create SubSP1---------
CREATE PROCEDURE dbo.SubSP1
AS
BEGIN
	PRINT 'Begining of SubSP1'
	--Try to access Non-Existent Table
	SELECT * FROM NonExistentTable
	PRINT 'End of SubSP1'
END
GO
-------Create SubSP2---------
CREATE PROCEDURE dbo.SubSP2
AS
BEGIN
	PRINT 'Inside SubSP2'
END

GO
-------Create MainSP---------
CREATE PROCEDURE dbo.MainSP
AS
BEGIN
	PRINT 'Begining of MainSP'
	EXEC dbo.SubSP1
	EXEC dbo.SubSP2	
	PRINT 'End of MainSP'
END
GO

Once the above stored procedures are created, let us execute the MainSP by the below statement and verify the result:

EXEC dbo.MainSP
GO

RESULT:

From the above SP execution results it is clear that the Access for a non existent table NonExistentTable from SubSP1 is not only terminating the statement which try’s to access this NonExistentTable table, but also the Subsequent statements in the SubSP1’s scope. But Sql Server continues with the execution of the subsequent statements which are present in the in the MainSP which has called this SubSP1 and also the SubSP2 is called from the MainSP.

Let us drop all the Stored Procedures created in this demo by using the below script:

DROP PROCEDURE dbo.SubSP2
DROP PROCEDURE dbo.SubSP1
DROP PROCEDURE dbo.MainSP
GO

Batch Abortion :

If Sql Server terminates the statement which raised the error and the subsequent statements in the batch then such behavior is termed as Batch Abortion. As shown in the below image the Statement-1 in SubSP1 is causing an error, in response to this Sql Server terminates not only the statement that raised the error i.e. Statement-1, but also terminates all the subsequent statements in the SubSP1 and it will not execute the further statements/Sub Sp’s (For Example SubSP2) in the MainSP. Batch Abortion Errors ROLLS BACK any active transactions started prior to the statement which causes BATCH Abortion error.

We have already seen multiple Batch Abortion examples in the above DEMOs. Here let us see this behavior with stored procedures similar to the one explained in the above image. Let us execute the below script to create the three stored procedures for this demo:

------------Batch Abortion Demo --------------
-------Create SubSP1---------
CREATE PROCEDURE dbo.SubSP1
AS
BEGIN
	PRINT 'Begining of SubSP1'
	PRINT CAST('TEN THOUSAND' AS MONEY)
	PRINT 'End of SubSP1'
END
GO
-------Create SubSP2---------
CREATE PROCEDURE dbo.SubSP2
AS
BEGIN
	PRINT 'Inside SubSP2'
END
GO
-------Create MainSP---------
CREATE PROCEDURE dbo.MainSP
AS
BEGIN
	PRINT 'Begining of MainSP '
	EXEC dbo.SubSP1
	EXEC dbo.SubSP2	
	PRINT 'End of MainSP '
END
GO

Once the above stored procedures are created, let us execute the MainSP by the below statement and verify the result:

EXEC dbo.MainSP
GO

RESULT:

From the above SP execution results it is clear that the CONVERSION/CAST statement in the SubSP1 is causing the Batch Abortion.It is not only terminating the statement which raised the error but all the subsequent statement in the SubSP1 and the further statement in the MainSP which has called this SubSP1 and also the SubSP2 is not called from the MainSP post this error.

Let us drop all the Stored Procedures created in this demo by using the below script:

DROP PROCEDURE dbo.SubSP2
DROP PROCEDURE dbo.SubSP1
DROP PROCEDURE dbo.MainSP
GO

Connection Termination :

Errors with severity level 20-25 causes the Connection Termination. Only User with SysAdmin rights can raise error’s with these severity levels. As shown in the below image the Statement-1 in SubSP1 is causing an error with severity 20-25, in response to this Sql Server terminates not only the statement that raised the error i.e. Statement-1, but also terminates all the subsequent statements in the SubSP1 and it will not execute the further statements/Sub Sp’s (For Example SubSP2) in the MainSP. And finally terminates the connection. Note if there are any active Transactions which are started prior to the statement which caused the Connection Termination error, then Sql Server Takes care of Rolling Back all such transactions.

If we use RaiseError with WITH LOG option to raise an exception with severity level >=20 will result in a connection termination. Let us execute the below statement and observe the result:

RAISERROR('Connection Termination Error Demo', 20,1) WITH LOG
GO

RESULT: Connection is Terminated

Below query gives the list of Error’s that cause the Connection Termination.

SELECT * FROM sys.messages 
WHERE severity >= 20 and language_id =1033

Clean-UP:
Let us drop the database which we have created for this demo

--Drop the Database SqlHintsErrorHandlingDemo
USE TempDB
GO
DROP DATABASE SqlHintsErrorHandlingDemo

Let us know your feedback on this post, hope you have learnt something new. Please correct me if there are any mistakes in this post, so that I can correct it and share with the community.