The TRY CATCH block is used to handle exceptions in Transact-SQL code.
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.