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:
Post a Comment