The goal of this article is to provide a simple and easy to use error handling mechanism with minimum complexity.

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

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?

We can see this behavior with an example. As you 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?

--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?

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 do 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 debug 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 explain the THROW statement in the next section.

 

What are the benefits of THROW when we have RAISERROR?

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

Correct line number of the error!

As I said earlier this is the main advantage of using THROW. The following code will enlighten 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. 

 --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?

“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 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?

“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

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

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

---

See Also

Error Handling in SQL Server 2000

• Error Handling in SQL 2000 – a Background written by Erland Sommarskog
• Implementing Error Handling with Stored Procedures in SQL 2000 written by Erland Sommarskog

---

Other Languages

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

---