Question about old-school error handling Messages window behavior

• mtlsql

  SSCommitted

  Points: 1528

  More actions

  February 15, 2015 at 4:41 pm

  #98856

  I'm reading Wiley's 'SQL Server 2012 Bible' book (p. 414), and they're talking about old-school error handling (Pre-2005 TRY...CATCH blocks). I'm using SQL Server 2012, but typing SQL 2000 code in other words. I ran across a behavior in the Query Editor 'Messages' window that I found confusing.

  Below are two UPDATE statements that produce errors, but the error handling syntax is identical. The first produces error 547, but then continues to output to the 'Messages' window. The second also produces an error, this time error 241. However, the behavior is the 'Messages' window is different. It shows the error, and then stops. After the erroneous update, it never shows the portion of the error message, 'The statement has been terminated.', and doesn't show the values set in the variables.

  Why does the Messages window stop displaying messages after one type of error, but not after another?????

  Here's the code to copy-and-paste:

  [font="Courier New"]

  USE AdventureWorks2012;

  GO

  DECLARE @err INT;

  -- This raises an error; Can't update a PK value with an FK constraint...

  UPDATE HumanResources.Employee

  SET BusinessEntityID = 30000

  WHERE BusinessEntityID = 2;

  SET @err = @@ERROR;-- Must capture error immediately...

  PRINT @@ERROR; -- Acts on previous statement (SET @err = @@ERROR;), returns 0...

  PRINT @err; -- Returns error number 547...

  GO

  /* Messages result:

  Msg 547, Level 16, State 0, Line 4

  The UPDATE statement conflicted with the REFERENCE constraint "FK_EmployeeDepartmentHistory_Employee_BusinessEntityID".

  The conflict occurred in database "AW2012", table "HumanResources.EmployeeDepartmentHistory", column 'BusinessEntityID'.

  The statement has been terminated.

  0

  547

  */

  -- This raises an error; can't insert a string into a DATETIME column...

  DECLARE @err INT;

  UPDATE HumanResources.Department

  SET ModifiedDate = 'Dude...'

  WHERE DepartmentID = 10;

  SET @err = @@ERROR;

  -- No 'The statement has been terminated.' message???

  PRINT @@ERROR; -- Why doesn't this show in the messages window??? Should be 0...

  PRINT @err; -- Why doesn't this show in the messages window??? Should be 241...

  GO

  /* Messages window result:

  Msg 241, Level 16, State 1, Line 3

  Conversion failed when converting date and/or time from character string.

  */[/font]

  Thanks for your help!