TRY…CATCH Error & Exception Handling in SQL Server

Implementing acceptable Error Handling logic in T-SQL code is important to avoid unexpected errors breaking the system. Exception Handling using TRY…CATCH block in Microsoft SQL Server is fairly identical to exception handling in .NET and Java programming languages.

Basic Fundamental of TRY…CATCH block is a group of statements can be enclosed in a BEGIN TRY…END TRY block and if any error occurs within TRY block then control can be passed to a group of statements written in a BEGIN CATCH..END CATCH block to handle the generated errors in TRY block.

Syntax – TRY…CATCH Block – SQL Server

BEGIN TRY
    BEGIN
	 --Group of T-SQL Statements 
    END
END TRY
BEGIN CATCH
    BEGIN
	-- Group of T-SQL Statements executed when code fails in TRY 
	--Block and  control is passed to CATCH block
    END
END CATCH

Points to Ponder – TRY CATCH Exception Handling – SQL Server

1.If T-SQL code within a BEGIN TRY…END TRY block is successfully executed without any failures then the T-SQL code in the BEGIN CATCH..END CATCH block is never executed.

2.BEGIN CATCH..END CATCH block must be immediately associated next to END TRY block, writing any statements in between END TRY and BEGIN CATCH is not allowed, this will raise a syntax error.

3.If T-SQL code within BEGIN TRY…END TRY block is successfully executed then the control is passed to the immediate T-SQL code written after END CATCH block if any.

4.If any error arises then after executing the statements in BEGIN CATCH … END CATCH block the control is passed to the immediate statement written after END CATCH block.

5.BEGIN TRY .. END TRY block can be nested, we can have exception handling
TRY…CATCH block inside a TRY CATCH block.

Collecting Error Information In CATCH Block – SQL Server

1.ERROR_NUMBER() – To get the unique error number associated with the occurred error.

2.ERROR_SEVERITY() – To get the type of error that occurred, includes user fixable errors, fatal and non-fatal errors.

3.ERROR_STATE() – To get the state number of the error, to know the location where the error could have occurred.

4.ERROR_PROCEDURE() – Returns the name of the stored procedure or trigger where the error occurred causing the execution of CATCH block.

5.ERROR_MESSAGE() – Returns the entire error message, description about the error, like Divide by zero error encountered.

TRY..CATCH Block – Demo Examples – SQL Server

Let’s drive through demonstrating TRY..CATCH block with few examples to help us understand its uses and importance.

1.1 – Divide by Zero Error – TRY..CATCH Exception Handling – SQL Server

In the following example, we are trying to divide a number by zero to demonstrate the divide by zero error has occurred in a TRY block, and same error will be held by CATCH block and error related information will be displayed.

1.1 – Example In Action

BEGIN TRY
	SELECT 1 / 0; -- Divide by Zero Error;
END TRY
BEGIN CATCH
	SELECT 'We have encountered an Error'; 

	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

1.2 Successful – TRY…CATCH Block – SQL Server

Let’s create a table Employee with emphasis on a salary column, considering salary can not be zero in a real world, we are adding a CHECK constraint to make sure we always have non-zero values in a salary column.

Let’s insert a record with salary as 13,000, which should not raise any error hence control will not move to CATCH block, no statement will be executed within CATCH block.

In the following example, the SELECT statement within a TRY block successfully displayed, however, SELECT statement inside a CATCH block is not displayed since we didn’t see any error executing this insert statement.

1.2 – Example In Action

IF OBJECT_ID('Employee') IS NOT NULL
DROP TABLE Employee;

Create table Employee
(
    Id     INT,
    Name  VARCHAR(255),
    Salary INT CHECK (Salary > 0)
);

BEGIN TRY
    INSERT INTO Employee (Id, Name, Salary) 
    VALUES (100, 'Chetan', 13000); 
    
    SELECT 'I am in TRY Block';
END TRY
BEGIN CATCH
    SELECT 'Error --- I am in CATCH block';
END CATCH

1.3 Failure – TRY…CATCH Block – CHECK Constraint Error – SQL Server

In the following example, we are trying to insert an employee record with salary value as “0”, which should violate the CHECK constraint and raise an error.

Below code will raise an error due to the CHECK constraint violation hence message after Insert statement is not displayed and none another statement will be executed, and immediately the control is passed to CATCH block.

We can see SELECT statement message inside CATCH block is displayed with the error message.

1.3 – Example In Action

BEGIN TRY
	INSERT INTO Employee (Id, Name, Salary) 
	VALUES (100, 'Vish', 0); 
	SELECT 'I am in TRY Block'; --Not Shown
END TRY
BEGIN CATCH
	SELECT 'Error --- I am in catch block'; --Shown
	SELECT  ERROR_MESSAGE(); --Shown
END CATCH

1.4 – TRY…CATCH Block In a TRANSACTION

Consider a scenario, we want to insert more than one record in employee table and to make sure all the records are successfully inserted in employee table instead it should rollback every insert statements if any of the records have had any error.

Let’s construct a TRY..CATCH block with Transaction to handle errors and rollback the entire batch of statements. We can see that 3rd Insert statement is going to violate the check constraint hence this will raise an error and control will be passed to CATCH block where the transaction will be rollbacked and no insert statement will be committed in a table.

@@TRANCOUNT – Keep tracks of the number of begin transaction statements have occurred on the current connection.

BEGIN TRY
	BEGIN TRANSACTION
	INSERT INTO Employee (Id, Name, Salary)  VALUES (102, 'Niraj', 10000); 
	INSERT INTO Employee (Id, Name, Salary) VALUES  (103, 'Chetan',  18000); 
	INSERT INTO Employee (Id, Name, Salary) VALUES  (104, 'Vishal', 0); 
END TRY
BEGIN CATCH
	IF(@@TRANCOUNT > 0)
	ROLLBACK TRANSACtION;	

	SELECT 'Transaction is Rollback, In Catch Block';

	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

IF(@@TRANCOUNT > 0)
COMMIT TRANSACTION;

Error

The INSERT statement conflicted with the CHECK constraint “CK__Employee__Salary__79FD19BE”. The conflict occurred in database “PraticeDb”, table “dbo.Employee”, column ‘Salary’.

You can also view Tech-Recipes.com SQL Server Archives to find out more useful posts.