Sunday, June 4, 2006

SQL 2005+ - TRY CATCH Error detection

Prior to SQL 2005, errors in TSQL code had to be tested for and captured by @@ERROR.
SQL 2005 implements the TRY CATCH syntax in the same way as javascript, c++ and subsequently the .NET languages.

For example, using AdventureWorks, I purposely attempt to delete a record I shouldnt, and receive an error -

DELETE Person.Address WHERE AddressID = 1

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeAddress_Address_AddressID". The conflict occurred in database "AdventureWorks", table "HumanResources.EmployeeAddress", column 'AddressID'.
The statement has been terminated.

@@ERROR only contains the error in the very next statement after the error, hence to both examine & display the value I have to assign it to a local variable -

TSQL Programming to catch the error -

DECLARE @ErrorResult INTEGER
DELETE Person.Address WHERE AddressID = 1
SET @ErrorResult = @@ERROR
IF @ErrorResult <>0
 BEGIN
  PRINT 'Error ' + CAST(@ErrorResult AS VARCHAR(10)) + ' : Could not Delete record'
 END


Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeAddress_Address_AddressID". The conflict occurred in database "AdventureWorks", table "HumanResources.EmployeeAddress", column 'AddressID'.
The statement has been terminated.
Error 547 : Could not Delete record

Note : both the SQL Error and my message are returned.

Using TRY/CATCH however, the error can be caught and the script continues -

BEGIN TRY
   -- Attempt delete of referenced record.
    DELETE Person.Address WHERE AddressID = 1
END TRY
BEGIN CATCH
    -- Do alternate action.
    PRINT 'Could not Delete record'
END CATCH


Much tidier, only my error message is returned -

Error 547 : Could not Delete record

No comments: