When SQL Server completes the execution of a T-SQL statement, @@ERROR is set to the value 0 if the statement is executed successfully. Otherwise SQL Server returns an error number. Remember that @@ERROR returns the number of the error message until another T-SQL statement is executed. Because @@ERROR is cleared and reset on each statement execution, you should check it immediately following the statement being validated. Alternatively, you can save it to a local variable that can be checked later.
Text associated with an @@ERROR error number can be viewed in the sysmessages system table. A typical usage to check for a check constraint violation (error number 547) in an UPDATE statement is shown below:
USE MyTableGOUPDATE FieldNameID SET id = '100' WHERE id = "100"IF @@ERROR = 547print "A check constraint violation or conflict
occurred in database table"