SQL ERROR HANDLING WITH TRY..CATCH

The TRY CATCH block is used to handle exceptions in Transact-SQL code.

SYNTAX

BEGIN TRY
---Sql code
END TRY

BEGIN CATCH
--- Sql code
END CATCH

The sqlcode between the BEGIN TRY and END TRY block is the code that you want to trace for an error.

So, if an error occurs inside the TRY block, the control is passed to the CATCH block and then it executes the code line by line.

Lets look at an example of TRY .. CATCH in SQL Server.

In following SQL statement produce an error of divide by zero. 

select 1/0 ;

 

SELECT 1/0 AS error;
END TRY 
BEGIN CATCH 
SELECT 'Divide by zero error occured' AS Messagetext 
END CATCH

As you can see, when an error occurred in try block the control is transferred immediately to Catch block to handle it and catch block executes the code written within Catch block and returns the custom error message .

So this time you will not get any error in T-SQL code statement as it is handled by TRY..CATCH block .

You can also return the actual system generated error message using SQL default function that returns an error status as given below.

• Error_Number – It returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to execute.
• Error_Message – It returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to execute.
• Error_Line – It returns the line number of occurrence of an error that caused the CATCH block of a TRY…CATCH construct to execute.
• Error_Severity – It returns the severity value of the error where an error occurs, if that error caused the CATCH block of a TRY…CATCH construct to execute.
• Error_State – It returns the state number of the error that caused the CATCH block of a TRY…CATCH construct to be run.
• Error_procedure – It returns the name of the stored procedure or trigger where an error occurs, if that error caused the CATCH block of a TRY…CATCH construct to execute.

Lets, create a stored Procedure that accepts two input parameter values.

CREATE  PROC  proc_errorhandling (@input1 int, @intput2 int)

AS 

BEGIN TRY

SELECT 1/0 as ERROR;

END TRY

BEGIN CATCH

SELECT  ERROR_MESSAGE() AS ErrorMessage , ERROR_NUMBER() AS ErrorNumber ,

ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState ,

ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine 

END CATCH

Once, you execute the stored procedure by passing parameter’s values as 1 and 0 to produce a divide by zero error that will be handled by TRY.. CATCH block.

Exec proc_errorhandling  1  , 0

As you can see, divide by zero is handled by TRY.. CATCH block and returned complete error with Error message, Error Number, Error Severity, Error state, Error Procedure and Error Line.

Also Read..

Dynamic pivoting

SQL Server ROLLUP

SQL Server Cube

SQL Server Grouping Sets

SQL User Defined Functions