quote:

---

A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

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 does not contain its own TRY…CATCH construct, the error returns control to the CATCH block associated with the TRY block that contains the EXECUTE statement.
* If the stored procedure contains a TRY…CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.

Errors Unaffected by a TRY…CATCH Construct

TRY…CATCH constructs do not 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 is not disrupted, TRY…CATCH will handle the error.
* Attentions, such as client-interrupt requests or broken client connections.
* When the session is ended by a system administrator by using the KILL statement.

The following types of errors are not 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.

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

---

CREATE PROCEDURE dbo.k_SP_Test1
AS
PRINT 'k_SP_Test1 START'

SELECT TOP 1 * FROM dbo.NonExistingTable

PRINT 'k_SP_Test1 END'

RETURN 0
GO

CREATE PROCEDURE dbo.k_SP_Test2
AS
PRINT 'k_SP_Test2 START'

DECLARE	@intErrNo1 int,
	@intErrNo2 int

EXEC @intErrNo1 = dbo.k_SP_Test1
SELECT	@intErrNo2 = @@ERROR

PRINT 'k_SP_Test2 END - @intErrNo1=' + COALESCE(CONVERT(varchar(20), @intErrNo1), '[NULL]') 
	+ ', @intErrNo2=' + COALESCE(CONVERT(varchar(20), @intErrNo2), '[NULL]')

RETURN 0
GO

PRINT 'EXEC START'

DECLARE	@intErrNo1 int,
	@intErrNo2 int

EXEC @intErrNo1 = dbo.k_SP_Test2
SELECT	@intErrNo2 = @@ERROR

PRINT 'EXEC END - @intErrNo1=' + COALESCE(CONVERT(varchar(20), @intErrNo1), '[NULL]') 
	+ ', @intErrNo2=' + COALESCE(CONVERT(varchar(20), @intErrNo2), '[NULL]')
GO

DROP PROCEDURE dbo.k_SP_Test1
GO
DROP PROCEDURE dbo.k_SP_Test2
GO