Detecting and Reporting SQL-Server Errors

Describe how to handle errors in your Transact-SQL code.
An error is the result of a Transact-SQL statement that does not execute successfully. Errors are caused by a whole range of factors, including invalid syntax, invalid object names, and key violations. Errors can occur at any time, in any place, and are often not caused by the programmer. For example, one of your queries or stored procedures may accesses a table that was renamed by someone else, thereby “breaking” your code.

You can determine whether an error occurred by testing the @@error system function (formally known as a sglobal variable) right after the Transact-SQL statement in question.
If no error has occurred, @@error will equal zero. If an error has occurred, @@error will return an error number.

Every error number and its DESCRIPTION are stored in a system catalog named sysmessages, which resides in the master database. To view all errors in the sysmessages system catalog, issue the following Transact-SQL statement:

select * from master.dbo.sysmessages

Handling errors refers to the steps you take when an error occurs. You may opt to do nothing. In other words, however SQL Server would normally handle the error may be fine. However, you may want your Transact-SQL code to take a different path if an error occurs.

To identify that an error has occurred, you can use the following statement:

UPDATE Employees
SET Phone = ‘555-1212’
WHERE EmployeeID = 17
If @@error <> 0
  SELECT ‘Error Occurred!’

As a result of the above code, if an error occurs while updating the phone number for employee 17, text is returned to the client application indicating that an error occurred.

On the other hand, you might care about a specific error.
For example, suppose you want to take a specific action if the foreign key is violated in an update statement. According to the sysmessages system catalog, you would test for error number 547, like this:

UPDATE employees
SET DepartmentID = @Dept
WHERE DepartmentID = 101
If @@error = 547
  SELECT 'Department number' + cast(@Dept as varchar) 
  + ' does not exist'