CODEWITHSUNDEEP
sql-servert-sql
psj01
psj01

Reputation: 3245

T-SQL stored procedure exception due to primary key constraint

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

Upvotes: 0

Views: 805

Answers (2)

Cee McSharpface
Cee McSharpface

Reputation: 8725

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.

Upvotes: 1

dfundako
dfundako

Reputation: 8324

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

Upvotes: 2

Related Questions