Obtaining Error Details
When you capture an error, it is often useful to interrogate it and perform different actions according to the error encountered. T-SQL provides six system functions that allow you to obtain further error information. The details returned are always those of the error that cause the catch block to be executed. The functions are:
- ERROR_MESSAGE. This function returns the full text of the error message as it would be seen if unhandled. It may include additional information, such as the values passed to parameters if appropriate. You may decide to add this message to logs to help debug software.
- ERROR_NUMBER. This function returns the unique error number of the raised error. If you have several possible error recovery strategies, you may check this number in an IF-ELSE structure to determine which to use.
- ERROR_LINE. This function returns the line number of the statement in the script or procedure that caused the error.
- ERROR_PROCEDURE. If the error occurred whilst processing a stored procedure or trigger, this function returns the name of the procedure. In all other circumstances the function returns NULL.
- ERROR_SEVERITY. This function returns a number that represents the severity of the error. The highest severity is 25. Values of between 17 and 25 indicate software or hardware failures, with levels below 20 considered to be recoverable. Levels 11 to 16 are used for user-generated errors that can be corrected. Level 10 errors provide information only and will not cause control to move to the catch block.
- ERROR_STATE. This function returns a unique state code for the error. This information is useful when determining whether a problem that is being encountered is the same as one reported in the Microsoft Knowledge Base.
The script below shows the use of the ERROR_MESSAGE function to output the details of the division by zero error.
BEGIN TRY
SELECT 1 / 0
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
/* OUTPUT
Divide by zero error encountered.
*/Limitations
The try / catch block has several limitations that you should bear in mind. There are some errors, specifically those that cause client disconnection or those with a severity of ten or lower, that cannot be captured. Secondly, there are some places where try / catch blocks may not be used. These include within the code of a user-defined function. Finally, although GOTO statements can be used to jump within a try or catch block or out of a try or catch block, they cannot be used to jump into either block.
3 October 2010