Home » SQL ERROR HANDLING WITH TRY..CATCH

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 ;

 

BEGIN TRY
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 ErrorMessageERROR_NUMBER() AS ErrorNumber ,

ERROR_SEVERITY() AS ErrorSeverityERROR_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.




2 thoughts on “SQL ERROR HANDLING WITH TRY..CATCH”

    1. Hi Ganesh,

      Whatever the statement would be there in try block are traced for an error, suppose you have any insert statement inside try block and you have wrongly passed any string value to integer column during record insertion, in this case it throws a conversion error of datatype varchar to integer so in this case control are passed to catch block to handel an error .

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.