This is the second article of four articles in the series of articles on Exception Handling in SQL Server on sqlhints.com. In this post we look at TRY…CATCH and Error Functions.

TRY…CATCH

TRY…CATCH is the Structured Error handling construct introduced in SQL Server 2005. It is similar to the one which we have in C#, but it doesn’t have the FINALLY block. If any error is raised by the statement in the TRY block then the control is immediately passed to the CATCH block. If none of the statement in the TRY block raises any exception then the CATCH block will not be executed.

As soon as the error is encountered in the TRY block, SQL Server terminates the Statement which raised the error, no subsequent statements in the TRY block are executed and the control is passed to the CATCH block.

In this article at this website I will not go through all of the demo examples presented on the sqlhints.com website for this second article.

In this demo number 4 (the forth one on this topic at sqlhints.com) we will see how we can use the Transactions with TRY…CATCH constructs.

DELETE FROM dbo.Account
GO
PRINT 'BEFORE TRY'
BEGIN TRY
    BEGIN TRAN
	PRINT 'First Statement in the TRY block'
	INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1',  10000)
	UPDATE dbo.Account SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) WHERE AccountId = 1
	INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2',  20000)
	PRINT 'Last Statement in the TRY block'
    COMMIT TRAN
END TRY
BEGIN CATCH
    PRINT 'In CATCH Block'
    IF(@@TRANCOUNT > 0)
        ROLLBACK TRAN
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO

Here are the results we get.

First Statement in the TRY block

(1 row(s) affected)

(0 row(s) affected)
In CATCH Block
After END CATCH
AccountId   Name                                               Balance
----------- -------------------------------------------------- ---------------------

(0 row(s) affected)

In the above example, @@TRANCOUNT in the CATCH block identifies whether we have any active transactions. If true, we roll back the transaction. Because of this we don’t see any records in the Account Table. The UPDATE statement causes an error.

Error Functions

In this next example, we try to divide by zero and get the error messages back with the use of error functions.

BEGIN TRY
 SELECT 5/0
END TRY
BEGIN CATCH
 PRINT '*************Error Detail****************'
 PRINT 'Error Number  :' + CAST(ERROR_NUMBER() AS VARCHAR)
 PRINT 'Error Severity:' + CAST(ERROR_SEVERITY() AS VARCHAR)
 PRINT 'Error State   :' + CAST(ERROR_STATE() AS VARCHAR)
 PRINT 'Error Line    :' + CAST(ERROR_LINE() AS VARCHAR)
 PRINT 'Error Message :' + ERROR_MESSAGE()
END CATCH

-----------

(0 row(s) affected)

*************Error Detail****************
Error Number  :8134
Error Severity:16
Error State   :1
Error Line    :2
Error Message :Divide by zero error encountered.

Below is the list of ERROR FUNCTIONS which we can use in the CATCH block to get the details of the Error which resulted in transferring the control from the TRY block to the CATCH block. These functions can used only in the scope of the CATCH block. They will not return the value outside the CATCH block.

• ERROR_NUMBER() : Returns the Error Number.
• ERROR_SEVERITY() : Returns the Severity of the Error.
• ERROR_STATE() : Returns the State of the Error.
• ERROR_PROCEDURE(): Returns the name of the SP/UDF in which the error occurred.
• ERROR_LINE() : Returns the line number of the Sql statement which raised the error.
• ERROR_MESSAGE() : Returns the error message.

TRY…CATCH construct catches all the errors except the Connection Termination errors. This implies any error in the TRY block with severity level between 11-19 causes SQL Server to transfer the control to the CATCH block. One exception is the Scope Abortion error (i.e. Deferred Name resolution) which is not a connection termination error but can’t be Trapped by the TRY…CATCH construct. The example below shows this.

PRINT 'BEFORE TRY'
BEGIN TRY
  PRINT 'First Statement in the TRY block'
  SELECT * FROM NONExistentTable
  PRINT 'Last Statement in the TRY block'
END TRY
BEGIN CATCH
  PRINT 'In the CATCH block'   
END CATCH
PRINT 'After END CATCH'
GO

BEFORE TRY
First Statement in the TRY block
Msg 208, Level 16, State 1, Line 4
Invalid object name 'NONExistentTable'.

Cannot Start Another Try Without a Catch

You cannot start another TRY clause without first having a CATCH clause. The following code is illegal.

begin try
	print 'first try';
end try
begin try  -- Incorrect syntax near 'begin try'. Expecting BEGIN_CATCH
	print 'second try';
end try

Multiple TRY…CATCH

You may have a TRY…CATCH clause following another TRY…CATCH clause. After the END TRY the next statement must be BEGIN CATCH.

begin try
	print 'first try'  -- executed
	select 6/0;
	print 'first try after error' -- not executed!
end try
-- you cannot execute any code here because SQL Server 
-- expects to see begin catch.
begin catch
	print 'inside first catch!'
	SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_MESSAGE() AS ErrorMessage; 
end catch
-- -----------------------------------------
begin try
	print 'second try'  -- executed
end try
begin catch
	print 'inside second catch!'
	SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_MESSAGE() AS ErrorMessage; 
end catch

Here are the results in SSMS if all output is set to Results to Text.

first try

-----------

(0 rows affected)

inside first catch!
ErrorNumber ErrorMessage
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8134        Divide by zero error encountered.

(1 row affected)

second try