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

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"
See also  5 Ways to Improve Customer Experience

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist