T-SQL stored procedure exception due to primary key constraint

Question

I have a stored procedure to insert a record into two tables.

First I have it insert into tableA, and then into tableB.

I am a little confused because if the insert into tableA fails, I expect it to error out and not run the next statement. But that doesn't seem to be the case..

If an exception happens it still run the statements below it?...

  BEGIN
      INSERT INTO TABLEA (Counter) VALUES (1989); -- duplicate error!
      INSERT INTO TABLEB (Counter) VALUES (2010);
  END

The error I get is:

Violation of PRIMARY KEY constraint 'PK_TABLEA'. Cannot insert duplicate key in object 'dbo.TABLEA'. The duplicate key value is (1989). The statement has been terminated.

I do get an error when I call this stored procedure in my C# console application though. That's why I'm confused as to why its raising the exception.. but continuing with the next statements...

welcome to the wonderful world of tsql error handling. [read here](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4ed06985-c9c8-42a5-a621-a855634efec2/continue-executing-sql-statements-despite-errors) and [here](http://do.whileloop.org/2016/04/12/sql-server-stored-procedures-continue-execution-on-error/) essence "some errors abort only the current *statement*" — Cee McSharpface, Jun 08 '18 at 20:17
To defend tsql, the idea is, if you have the first line error the next line(s) maybe be to log the error, so if everything were to error out, you would lose that ability. — Holmes IV, Jun 08 '18 at 20:19
But then how am i getting the error in my console app where I do cmd.ExecuteNonQuery(); ... — psj01, Jun 08 '18 at 20:23
the errors raised during execution are not lost, they are transported to the client and converted to .NET exceptions. they just do not halt the tsql execution. — Cee McSharpface, Jun 08 '18 at 20:25
`SET XACT_ABORT ON` is your friend. As is reading every article in [this series](http://sommarskog.se/error_handling/Part1.html). — Jeroen Mostert, Jun 08 '18 at 20:26
Is it just sql server that does this?... I don't think i seen this before when i was working with oracle database... — psj01, Jun 08 '18 at 20:26
Even though I have very little experience with Oracle, I'd be surprised if it has more mysterious error handling than SQL Server. That's how bad it is in SQL Server... — Jeroen Mostert, Jun 08 '18 at 20:28
oracle 11g and 12c do not do this by default, but when you start using its exception handling capabilities, [stuff like this](https://stackoverflow.com/questions/37824023/whenever-sqlerror-never-works?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) and conditions like `WHENEVER SQLEXCEPTION` working in all execution contexts except SQL*plus... — Cee McSharpface, Jun 08 '18 at 20:39

score 2 · Answer 1 · answered Jun 08 '18 at 20:26

In TSQL, you can use TRY/CATCH for this. When an error occurs, the subsequent operations can continue to run and commit to tables.

The way you are doing it now:

CREATE TABLE #test1 (ID INT PRIMARY KEY)
GO 
CREATE TABLE #test2 (ID INT PRIMARY KEY)
GO 

BEGIN TRAN;
INSERT INTO #test1 (ID)
VALUES 
(1);

INSERT INTO #test2 (ID)
VALUES 
(1)
;
COMMIT

BEGIN TRAN;
INSERT INTO #test1 (ID)
VALUES 
(1);

INSERT INTO #test2 (ID)
VALUES 
(2);

COMMIT

SELECT * 
FROM #test1
SELECT * 
FROM #test2

With a try and catch block, you can prevent both statements from occurring if one happens to error.

CREATE TABLE #test1 (ID INT PRIMARY KEY)
GO 

CREATE TABLE #test2 (ID INT PRIMARY KEY)
GO 


BEGIN TRAN;

INSERT INTO #test1 (ID)
VALUES 
(1);

INSERT INTO #test2 (ID)
VALUES 
(1)
;

COMMIT


BEGIN TRY
INSERT INTO #test1 (ID)
VALUES 
(1);

INSERT INTO #test2 (ID)
VALUES 
(2)

END TRY

BEGIN CATCH
print('bad things happened')
END CATCH


SELECT * 
FROM #test1
SELECT * 
FROM #test2

score 1 · Accepted Answer · answered Jun 08 '18 at 20:28

This behavior is a legacy from earlier versions of t-sql and about not breaking backwards compatibility.

A more consequent and predictable behavior can be achieved by setting the property XACT_ABORT to on

SET XACT_ABORT ON

A very good article on this and other error-handling related stuff: http://www.sommarskog.se/error_handling/Part1.html

A related article discusses this in connection with stored procedures: What is the benefit of using "SET XACT_ABORT ON" in a stored procedure?

This also includes the advice to always set XACT_ABORT ON before executing tsql scripts which consist of more than one command, or involve a transaction.

T-SQL stored procedure exception due to primary key constraint

2 Answers2