error-state-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
ERROR_STATE (Transact-SQL)	ERROR_STATE (Transact-SQL)	markingmyname	maghan	03/16/2017	sql	t-sql	reference	ERROR_STATE_TSQL ERROR_STATE	messages [SQL Server], state ERROR_STATE function errors [SQL Server], state TRY...CATCH [SQL Server] CATCH block states [SQL Server], error numbers	TSQL	>= aps-pdw-2016 || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current ||=fabric

ERROR_STATE (Transact-SQL)

[!INCLUDE sql-asdbmi-asa-pdw-fabricse-fabricdw]

Returns the state number of the error that caused the CATCH block of a TRY...CATCH construct to be run.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

ERROR_STATE ( )  

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Return Types

int

Return Value

When called in a CATCH block, returns the state number of the error message that caused the CATCH block to be run.

Returns NULL if called outside the scope of a CATCH block.

Remarks

Some error messages can be raised at multiple points in the code for the [!INCLUDEmsCoName] [!INCLUDEssNoVersion] [!INCLUDEssDE]. For example, an "1105" error can be raised for several different conditions. Each specific condition that raises the error assigns a unique state code.

When viewing databases of known issues, such as the [!INCLUDEmsCoName] Knowledge Base, you can use the state number to determine if the recorded issue might be the same as the error you have encountered. For example, if a Knowledge Base article discusses an 1105 error message with a state of 2, and the 1105 error message you received had a state of 3, your error probably had a different cause than the one reported in the article.

A [!INCLUDEssNoVersion] support engineer can also use the state code from an error to find the location in the source code where that error is being raised, which may provide additional ideas on how to diagnose the problem.

ERROR_STATE may be called anywhere within the scope of a CATCH block.

ERROR_STATE returns the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block. This is in contrast to functions like @@ERROR, which only returns the error number in the statement immediately after the one that causes an error, or in the first statement of a CATCH block.

In nested CATCH blocks, ERROR_STATE returns the error state specific to the scope of the CATCH block in which it is referenced. For example, the CATCH block of an outer TRY...CATCH construct could have a nested TRY...CATCH construct. Within the nested CATCH block, ERROR_STATE returns the state from the error that invoked the nested CATCH block. If ERROR_STATE is run in the outer CATCH block, it returns the state from the error that invoked that CATCH block.

Examples

A. Using ERROR_STATE in a CATCH block

The following example shows a SELECT statement that generates a divide-by-zero error. The state of the error is returned.

BEGIN TRY  
    -- Generate a divide by zero error  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    SELECT ERROR_STATE() AS ErrorState;  
END CATCH;  
GO  

B. Using ERROR_STATE in a CATCH block with other error-handling tools

The following example shows a SELECT statement that generates a divide-by-zero error. Along with the error state, information that relates to the error is returned.

BEGIN TRY  
    -- Generate a divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    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;  
END CATCH;  
GO  

Examples: [!INCLUDEssazuresynapse-md] and [!INCLUDEssPDW]

C. Using ERROR_STATE in a CATCH block with other error-handling tools

The following example shows a SELECT statement that generates a divide-by-zero error. Along with the error state, information that relates to the error is returned.

BEGIN TRY  
    -- Generate a divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    SELECT  
        ERROR_NUMBER() AS ErrorNumber,  
        ERROR_SEVERITY() AS ErrorSeverity,  
        ERROR_STATE() AS ErrorState,  
        ERROR_PROCEDURE() AS ErrorProcedure,  
        ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;  
GO  

See Also

sys.messages (Transact-SQL)
TRY...CATCH (Transact-SQL)
ERROR_LINE (Transact-SQL)
ERROR_MESSAGE (Transact-SQL)
ERROR_NUMBER (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL)
RAISERROR (Transact-SQL)
@@ERROR (Transact-SQL)
Errors and Events Reference (Database Engine)