TRY...CATCH (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric
Implements error handling for Transact-SQL that is similar to the exception handling in the C# and Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY
block. If an error occurs in the TRY
block, control is usually passed to another group of statements that is enclosed in a CATCH
block.
Transact-SQL syntax conventions
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
[ ; ]
Any Transact-SQL statement.
Any group of Transact-SQL statements in a batch or enclosed in a BEGIN...END
block.
A TRY...CATCH
construct catches all execution errors that have a severity higher than 10 that don't close the database connection.
A TRY
block must be immediately followed by an associated CATCH
block. Including any other statements between the END TRY
and BEGIN CATCH
statements generates a syntax error.
A TRY...CATCH
construct can't span multiple batches. A TRY...CATCH
construct can't span multiple blocks of Transact-SQL statements. For example, a TRY...CATCH
construct can't span two BEGIN...END
blocks of Transact-SQL statements, and can't span an IF...ELSE
construct.
If there are no errors in the code that is enclosed in a TRY
block, when the last statement in the TRY
block finishes, control passes to the statement immediately after the associated END CATCH
statement.
If there's an error in the code that is enclosed in a TRY
block, control passes to the first statement in the associated CATCH
block. When the code in the CATCH
block finishes, control passes to the statement immediately after the END CATCH
statement.
Note
If the END CATCH
statement is the last statement in a stored procedure or trigger, control is passed back to the statement that called the stored procedure or fired the trigger.
Errors trapped by a CATCH
block aren't returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH
block must do so by using mechanisms such as SELECT
result sets or the RAISERROR
and PRINT
statements.
TRY...CATCH
constructs can be nested. Either a TRY
block or a CATCH
block can contain nested TRY...CATCH
constructs. For example, a CATCH
block can contain an embedded TRY...CATCH
construct to handle errors encountered by the CATCH
code.
Errors encountered in a CATCH
block are treated like errors generated anywhere else. If the CATCH
block contains a nested TRY...CATCH
construct, any error in the nested TRY
block passes control to the nested CATCH
block. If there's no nested TRY...CATCH
construct, the error is passed back to the caller.
TRY...CATCH
constructs catch unhandled errors from stored procedures or triggers executed by the code in the TRY
block. Alternatively, the stored procedures or triggers can contain their own TRY...CATCH
constructs to handle errors generated by their code. For example, when a TRY
block executes a stored procedure and an error occurs in the stored procedure, the error can be handled in the following ways:
If the stored procedure doesn't contain its own
TRY...CATCH
construct, the error returns control to theCATCH
block associated with theTRY
block that contains theEXECUTE
statement.If the stored procedure contains a
TRY...CATCH
construct, the error transfers control to theCATCH
block in the stored procedure. When theCATCH
block code finishes, control is passed back to the statement immediately after theEXECUTE
statement that called the stored procedure.
GOTO
statements can't be used to enter a TRY
or CATCH
block. GOTO
statements can be used to jump to a label inside the same TRY
or CATCH
block or to leave a TRY
or CATCH
block.
The TRY...CATCH
construct can't be used in a user-defined function.
In the scope of a CATCH
block, the following system functions can be used to obtain information about the error that caused the CATCH
block to be executed:
Function | 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 NULL
if they're called outside the scope of the CATCH
block. Error information can be retrieved by using these functions from anywhere within the scope of the CATCH
block. For example, the following script shows a stored procedure that contains error-handling functions. In the CATCH
block of a TRY...CATCH
construct, the stored procedure is called and information about the error is returned.
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID('usp_GetErrorInfo', 'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- 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;
The ERROR_*
functions also work in a CATCH
block inside a natively compiled stored procedure.
TRY...CATCH
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,
TRY...CATCH
handles the error.Attentions, such as client-interrupt requests or broken client connections.
When a system administrator uses the
KILL
statement to end the session.
The following types of errors aren't handled by a CATCH
block when they occur at the same level of execution as the TRY...CATCH
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 sp_executesql
or a user-defined stored procedure) inside the TRY
block, the error occurs at a lower level than the TRY...CATCH
construct and will be handled by the associated CATCH
block.
The following example shows how an object name resolution error generated by a SELECT
statement isn't caught by the TRY...CATCH
construct, but is caught by the CATCH
block when the same SELECT
statement is executed inside a stored procedure.
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
The error isn't caught and control passes out of the TRY...CATCH
construct to the next higher level.
Running the SELECT
statement inside a stored procedure causes the error to occur at a level lower than the TRY
block. The error is handled by the TRY...CATCH
construct.
-- 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
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
If an error generated in a TRY
block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY
block causes a transaction to enter an uncommittable state when the error occurs inside a TRY
block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION
. The transaction can't execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION
. The XACT_STATE
function returns a value of -1
if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message is sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.
For more information about uncommittable transactions and the XACT_STATE
function, see XACT_STATE.
The following example shows a SELECT
statement that generates a divide-by-zero error. The error causes execution to jump to the associated CATCH
block.
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
The following example shows how a TRY...CATCH
block works inside a transaction. The statement inside the TRY
block generates a constraint violation error.
BEGIN TRANSACTION;
BEGIN TRY
-- Generate a constraint violation error.
DELETE
FROM Production.Product
WHERE ProductID = 980;
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;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
The following example shows how to use the TRY...CATCH
construct to handle errors that occur inside a transaction. The XACT_STATE
function determines whether the transaction should be committed or rolled back. In this example, SET XACT_ABORT
is ON
. This makes the transaction uncommittable when the constraint violation error occurs.
-- Check to see whether this stored procedure exists.
IF OBJECT_ID(N'usp_GetErrorInfo', N'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- 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_LINE() AS ErrorLine,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_MESSAGE() AS ErrorMessage;
GO
-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE
FROM Production.Product
WHERE ProductID = 980;
-- If the DELETE statement succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
-- Test XACT_STATE:
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means that there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT N'The transaction is in an uncommittable state. Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is committable.
-- You may want to commit a transaction in a catch block if you want to commit changes to statements that ran prior to the error.
IF (XACT_STATE()) = 1
BEGIN
PRINT N'The transaction is committable. Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
- THROW (Transact-SQL)
- Database Engine error severities
- ERROR_LINE (Transact-SQL)
- ERROR_MESSAGE (Transact-SQL)
- ERROR_NUMBER (Transact-SQL)
- ERROR_PROCEDURE (Transact-SQL)
- ERROR_SEVERITY (Transact-SQL)
- ERROR_STATE (Transact-SQL)
- RAISERROR (Transact-SQL)
- @@ERROR (Transact-SQL)
- GOTO (Transact-SQL)
- BEGIN...END (Transact-SQL)
- XACT_STATE (Transact-SQL)
- SET XACT_ABORT (Transact-SQL)