SQL Server – Handling Intermittent Processing Termination

error handlingsql server

DECLARE @Error int;
INSERT INTO Person.BusinessEntityContact
 (BusinessEntityID
 ,PersonID
 ,ContactTypeID)
 VALUES
 (0,0,1);
SELECT @Error = @@ERROR;
PRINT ‘The Value of @Error is ‘ + CONVERT(varchar, @Error);

I got :

Msg 547, Level 16, State 0, Line 4 The INSERT statement conflicted
with the FOREIGN KEY constraint
“FK_BusinessEntityContact_Person_PersonID”….The statement has been
terminated. The Value of @Error is 547

that means the process was still going on after the error was thrown.

But if I try create a table(run the below script twice to trigger the error) as:

CREATE TABLE OurIFTest(
 Col1 int PRIMARY KEY
 );
IF @@ERROR != 0
 PRINT 'Problems!';
ELSE
 PRINT 'Everything went OK!';

then I got

Msg 2714, Level 16, State 6, Line 2 There is already an object named
'OurIFTest' in the database.

which means the process was terminated when the error was thrown.

So why the process terminated for 'Create' but not for 'Insert'?

Best Answer

You can force the same behaviour accross errors with SET XACT_ABORT

SET XACT_ABORT ON;

From the documentation:

Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

Source

By default XACT_ABORT = OFF this means that different errors create different outcomes.

To force the behaviour on error to be the same, add SET XACT_ABORT ON; at the beginning of your script.

Testing with your example

SET XACT_ABORT ON;

DECLARE @Error int;
INSERT INTO Person.BusinessEntityContact
 (BusinessEntityID
 ,PersonID
 ,ContactTypeID)
 VALUES
 (0,0,1);
SELECT @Error = @@ERROR;
PRINT 'The Value of @Error is ‘ + CONVERT(varchar, @Error)'

With the only error shown:

Msg 547, Level 16, State 0, Line 17 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Person". The conflict occurred in database "my_test", table "Person.Person", column 'PersonID'.

Also note this important statement regarding error handling and SET XACT_ABORT

The THROW statement honors SET XACT_ABORT. RAISERROR does not. New applications should use THROW instead of RAISERROR.

Source

Related Question