Handling Database Engine Errors
Many errors raised by the SQL Server Database Engine can be captured and resolved programmatically. Error-handling capabilities are provided by the Transact-SQL language and by the data access application programming interfaces (APIs) that applications use to access data stored in the Database Engine.
Errors from the Database Engine can be handled on two levels:
- Errors can be handled in the Database Engine by adding error-handling code to Transact-SQL batches, stored procedures, triggers, or user-defined functions. The Transact-SQL error-handling mechanisms include the TRY…CATCH construct (see TRY...CATCH (Transact-SQL)), the RAISERROR statement, and the @@ERROR function.
- Errors can be returned to the calling application and handled in the application code. Each of the APIs that applications use to access the Database Engine provides mechanisms for passing the error information back to the application.
| Topic | Description |
|---|---|
Every Database Engine error contains the following attributes: an error number, a message string, severity, state, procedure name, and line number. |
|
Transact-SQL code can retrieve information about errors using the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR functions. |
|
SQL Server 2005 introduces the ability to process errors in Transact-SQL code using a TRY…CATCH construct similar to the exception-handling features of the Microsoft Visual C++ and Visual C# languages. When an error condition is detected in a TRY block, control is passed to a CATCH block where it can be processed. This is the primary mechanism for handling errors in the SQL Server 2005 Database Engine. |
|
The RAISERROR statement can be used to raise user-defined errors. It can also be used from a CATCH block to pass on to the application errors processed in the CATCH block. |
|
The PRINT statement can be used to return user-defined messages to the application. |
|
In earlier versions of SQL Server, the @@ERROR function is the primary means of detecting errors in Transact-SQL statements. TRY…CATCH constructs provide improved functionality. |
|
Each of the data access APIs, such as ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC), have mechanisms for reporting to the application any error information received from the Database Engine. |
|
Database Engine errors and user-defined error messages are raised with severities from 1 to 25. sp_addmessage and RAISERROR can be used to generate user-defined error messages. |