Test the Execution of a T-SQL statement Using the Value of @@ERROR

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"
Share the Post:
Share on facebook
Share on twitter
Share on linkedin


Recent Articles: