Retrieve error information

statement.

If the stored procedure contains a

construct, the error transfers control to

the

block in the stored procedure. When the

block code finishes, control is

passed back to the statement immediately after the

statement that called the

stored procedure.

statements can’t be used to enter a

or

block.

statements can be used to

jump to a label inside the same

or

block or to leave a

or

block.

The

construct can’t be used in a user-defined function.

In the scope of a

block, the following system functions can be used to obtain

information about the error that caused the

block to be executed:

Description

ERROR_NUMBER

Returns the number of the error.

ERROR_SEVERITY

Returns the severity.

ERROR_STATE

Returns the error state number.

ERROR_PROCEDURE

Returns the name of the stored procedure or trigger where the error occurred.

ERROR_LINE

Returns the line number inside the routine that caused the error.

ERROR_MESSAGE

Returns the complete text of the error message. The text includes the values

supplied for any substitutable parameters, such as lengths, object names, or times.

These functions return

if they’re called outside the scope of the

block. Error

information can be retrieved by using these functions from anywhere within the scope of the

block. For example, the following script shows a stored procedure that contains error-

handling functions. In the

block of a

construct, the stored procedure is

called and information about the error is returned.

Expand table

The

functions also work in a

block inside a

natively compiled stored procedure.

constructs don’t trap the following conditions:

Warnings or informational messages that have a severity of 10 or lower.

Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task

processing for the session. If an error occurs that has severity of 20 or higher and the

database connection isn’t disrupted,

handles the error.

Attentions, such as client-interrupt requests or broken client connections.

When a system administrator uses the

statement to end the session.

The following types of errors aren’t handled by a

block when they occur at the same

level of execution as the

construct:

Compile errors, such as syntax errors, that prevent a batch from running.

Errors that occur during statement-level recompilation, such as object name resolution

errors that occur after compilation because of deferred name resolution.

Object name resolution errors

These errors are returned to the level that ran the batch, stored procedure, or trigger.

If an error occurs during compilation or statement-level recompilation at a lower execution

level (for example, when executing

or a user-defined stored procedure) inside

the

block, the error occurs at a lower level than the

construct and will be

handled by the associated

block.

The following example shows how an object name resolution error generated by a

statement isn’t caught by the

construct, but is caught by the

block when

the same

statement is executed inside a stored procedure.

The error isn’t caught and control passes out of the

construct to the next higher

level.

Running the

statement inside a stored procedure causes the error to occur at a level

lower than the

block. The error is handled by the

construct.

TRY.CATCH

CATCH

CATCH

EXECUTE

GOTO

TRY

CATCH

GOTO

TRY

CATCH

TRY

CATCH

TRY.CATCH

CATCH

CATCH

NULL

CATCH

CATCH

CATCH

TRY.CATCH

-- Verify that the stored procedure does not already exist.
IF OBJECT_ID('usp_GetErrorInfo', 'P') IS NOT NULL
DROP
PROCEDURE usp_GetErrorInfo;
GO

ERROR_*

CATCH

TRY.CATCH

TRY.CATCH

KILL

CATCH

TRY.CATCH

-- Create procedure to retrieve error information.
CREATE
PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER()
AS
ErrorNumber,
ERROR_SEVERITY()
AS
ErrorSeverity,
ERROR_STATE()
AS
ErrorState,
ERROR_PROCEDURE()
AS
ErrorProcedure,
ERROR_LINE()
AS
ErrorLine,
ERROR_MESSAGE()
AS
ErrorMessage;
GO
BEGIN
TRY
-- Generate divide-by-zero error.
SELECT
1 / 0;
END
TRY
BEGIN
CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
END
CATCH;

sp_executesql

TRY

TRY.CATCH

CATCH

SELECT

TRY.CATCH

CATCH

SELECT

TRY.CATCH

SELECT

TRY

TRY.CATCH

BEGIN
TRY
-- Table does not exist; object name resolution
-- error not caught.
SELECT
*
FROM
NonexistentTable;
END
TRY
BEGIN
CATCH
SELECT
ERROR_NUMBER()
AS
ErrorNumber,
ERROR_MESSAGE()
AS
ErrorMessage;
END
CATCH
-- Verify that the stored procedure does not exist.
IF OBJECT_ID(N'usp_ExampleProc', N'P') IS NOT NULL
DROP
PROCEDURE usp_ExampleProc;
GO
-- Create a stored procedure that will cause an
-- object resolution error.
CREATE
PROCEDURE usp_ExampleProc
AS
SELECT
*
FROM
NonexistentTable;
GO
BEGIN
TRY
EXECUTE usp_ExampleProc;
END
TRY