Mssql error message

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.

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)