This post series is based on the series in sqlhints.com under the topic of exception handling.

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. We will use this database throughout this series of posts on exception handling.

SQL Server’s Response

--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
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1', 10000)
GO

We can add one record from the last part of the code above, but notice that the AccountID is a primary key and will not take duplicate values. Also notice that there is a constraint on the column Balance such that it must be positive. If we try to insert another record with the same AccountId we get the following error message.

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

Now let’s use DELETE FROM dbo.Account to clear the contents out of the table and start again. Let’s run the following code. In the following code batch, the first and third succeeds but the second one fails because of a primary key constraint. The AccountId must be unique for each row in the table.

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

We get the following error, and below that we have a look at what is in the table and notice that two rows have been added!

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK__Account__349DA5A647432891'. Cannot insert duplicate key in object 'dbo.Account'. The duplicate key value is (1).
The statement has been terminated.

(1 row(s) affected)

AccountId   Name                                               Balance
----------- -------------------------------------------------- ---------------------
1           Account1                                           10000.00
2           Account2                                           20000.00

(2 row(s) affected)

Statement Termination

From the example result it is clear that even though the Second insert statement is raising a primary key violation 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. Only the statement is terminated.

SET XACT_ABORT ON and Batch Abortion

Let’s make one change to the above example: SET XACT_ABORT ON. The result is that only the first INSERT succeeded. 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. 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.

DELETE FROM dbo.Account
GO
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

(2 row(s) affected)

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 6
Violation of PRIMARY KEY constraint 'PK__Account__349DA5A647432891'. Cannot insert duplicate key in object 'dbo.Account'. The duplicate key value is (1).

ABelow is how the table looks.

AccountId   Name                                               Balance
----------- -------------------------------------------------- ---------------------
1           Account1                                           10000.00

(1 row(s) affected)

Transactions

Let’s run the code inside a transaction and keep the XACT_ABORT ON. What will happen now? Will the first statement in the transaction go through? As we can see, no records were added. The first statement did not go through.

DELETE FROM dbo.Account
GO
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

AccountId   Name                                               Balance
----------- -------------------------------------------------- ---------------------

(0 row(s) affected)

It depends on the Type of Error!

The next example makes things a bit more complicated, and perhaps surprising. As a part of this demo we will verify what happens if a CONVERSION Error occurs within a batch of statement. Trying to convert the string ‘TEN THOUSAND’ to MONEY Type will result in a conversion error. Conversion errors cause a batch abortion. SQL Server terminates the statement which raised the error and the subsequent statements in the batch. Whereas PRIMARY KEY violation resulted in a Statement Termination. There are no transactions here.

DELETE FROM dbo.Account 
SET XACT_ABORT OFF
GO
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

Below I just combined the select statement with the error message so you can see it more easily.

AccountId   Name                                               Balance
----------- -------------------------------------------------- ---------------------
1           Account1                                           10000.00

(1 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)
Msg 235, Level 16, State 0, Line 7
Cannot convert a char value to money. The char value has incorrect syntax.

Conversion Errors inside a Transaction

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 one is that the Batch statement’s are executed within a Transaction. CONVERSION errors result in a BATCH Abortion and BATCH Abortion errors ROLL BACK any active transactions started prior to the BATCH Abortion error.

DELETE FROM dbo.Account 
SET XACT_ABORT OFF
GO
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

(1 row(s) affected)

(1 row(s) affected)
Msg 235, Level 16, State 0, Line 8
Cannot convert a char value to money. The char value has incorrect syntax.

AccountId   Name                                               Balance
----------- -------------------------------------------------- ---------------------

(0 row(s) affected)

Summary of SQL Server Error Actions

Following are the four different ways SQL Server responds (Error Actions) to errors:

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

Some of these error actions sqlhints.com has explained in the above demos using multiple examples. For now I will leave it up to the reader to go to sqlhints.com to read the rest of the article on this topic.