why the processing is sometimes terminated , sometimes not 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'?
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