This is the second article in the series of articles on Exception Handling in Sql Server. Below is the list of other articles in this series.

Part   I: Exception Handling Basics – MUST Read Article
Part  II: TRY…CATCH (Introduced in Sql Server 2005)
Part III: RAISERROR Vs THROW (Throw: Introduced in Sql Server 2012)
Part IV: Exception Handling Template

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.

SYNTAX:

BEGIN TRY
 -- T-Sql Statements
END TRY
BEGIN CATCH
 -- T-Sql Statements
 /*Control is passed to CATCH block only if
   there are any exceptions in the TRY block*/
END CATCH

Let us understand TRY…CATCH construct with below extensive list of examples.

To demonstrate this let us create a New Database and table as shown below:

--Create a New database for the Demo
CREATE DATABASE SqlHintsErrorHandlingDemo
GO
USE SqlHintsErrorHandlingDemo
GO
CREATE TABLE dbo.Account
(	
 AccountId INT NOT NULL PRIMARY KEY, 
 Name	 NVARCHAR (50) NOT NULL,
 Balance Money NOT NULL CHECK (Balance>=0)	
)
GO

As the Account table has Primary Key on the AccountId column, so it will raise an error if we try to duplicate the AccountId column value. And the Balance column has a CHECK constraint Balance>=0, so it will raise an exception if the value of Balance is <0.

DEMO 1:

Let us execute the below script and observe what will be the behavior of TRY..CATCH construct if none of the Statements in the TRY block raises any exception.

PRINT 'BEFORE TRY'
BEGIN TRY
 PRINT 'First Statement in the TRY block'	
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(1, 'Account1', 10000)
 PRINT 'Last Statement in the TRY block'
END TRY
BEGIN CATCH
	PRINT 'In CATCH Block'
END CATCH
PRINT 'After END CATCH'
GO

RESULT:

From the above result it is clear that, if the statements enclosed within TRY block doesn’t result in any errors then the control is not passed to the CATCH block instead the execution continues with immediate statement after the END CATCH statement.

Let us empty the Account Table before proceeding with the next DEMO.

DELETE FROM dbo.Account
GO

DEMO 2:

Let us execute the below script and observe what will be the behavior of TRY..CATCH construct. Here the Second INSERT statement results in a statement Terminating Primary Key Violation error.

PRINT 'BEFORE TRY'  
BEGIN TRY
 PRINT 'First Statement in the TRY block'
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(1, 'Account1',  10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(1, 'Duplicate', 10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(2, 'Account2',  20000)
 PRINT 'Last Statement in the TRY block'
END TRY
BEGIN CATCH
	PRINT 'In CATCH Block'	
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO

RESULT:

From the above result it is clear that 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.

Let us empty the Account Table before proceeding with the next DEMO.

DELETE FROM dbo.Account
GO

DEMO 3:

Let us execute the below script and observe what will be the behavior of TRY..CATCH construct. Here the Second statement (i.e. UPDATE statement) results in a Batch Abortion CONVERSION/CAST error.

PRINT 'BEFORE TRY'  
BEGIN TRY
 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'
END TRY
BEGIN CATCH
	PRINT 'In CATCH Block'	
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO

RESULT:

From the above result it is clear that 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.

Let us empty the Account Table before proceeding with the next DEMO.

DELETE FROM dbo.Account
GO

DEMO 4:

In this DEMO we will see how we can use the Transactions with TRY..CATCH constructs.

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

RESULT:

Here @@TRANCOUNT in the CATCH block identifies whether we have any active transactions, if yes we are rolling back the transaction. Because of this we don’t see any records in the Account Table.

DEMO 5:

In this demo we will see the set of ERROR FUNCTIONS which we can use in the CATCH block to get the details of the Error which resulted in transfering the control from the TRY block to the CATCH block

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

RESULT:

ERROR FUNCTIONS in Sql Server

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.

• 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.

These function can used only in the scope of the CATCH block. They will not return the value outside the CATCH block, below example demonstrates this:

SELECT 5/0
SELECT ERROR_NUMBER() ‘Error Number’, ERROR_MESSAGE() ‘Error Message’

RESULT:

DEMO 6:

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 below example demonstrates 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

RESULT:

Will cover few more examples of TRY…CATCH construct while discussing on the RAISERROR and THROW Statement.