Error Handling Using Try and Catch in SQL

Error Handling Using Try and Catch in SQL

In order to handle Errrors in your TSQL code SQl has provide a Tool Try and Catch that was introduced in SQL 2005.

You will place your code in Try Block which Starts with Begin Try if the code doesn't have any Error 

the complete Catch block is skipped, but in Case if there is an Error in the code inside Try Block then 

Control is Passed to Catch Block.

Let me Explain through an Example

Begin Try

Print 2/2

Print 'Succesful'

End  Try

Begin Catch

Print 'Unsuccesful'

End Catch

Output

1

Succesful

You noticed that there was no error in try Block so it skipped the catch Block Now we will generate an error in try Block in next statement

Begin Try

Print 2/0 --Divide by zero Exception

Print 'Succesful'

End  Try

Begin Catch

Print 'Unsuccesful'

End Catch

Output

UnSuccesful

There was a divide by zero exception in try block then the control went to Catch block where you have shown a custom message like Unsuccesful

If you want to Show sql Error Number and SQL Error Message instead of your custom message whenver an Error Occurs in Try BLock you can  Use like this

Begin Try

print 2/0

Print 'Succesful'

End  Try

Begin Catch

SELECT 

   ERROR_NUMBER() AS ErrorNumber

   ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

--For most of the error sql has stored an error no. and message based on that it's displaying no. and msg

Output

ErrorNumber ErrorMessage

8134          Divide by zero error encountered.

How to Use Error Handling with DML Operations

For this I will create a Table

create table Employee_ErrorHandling

(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)

Now the Insert statement i will keep inside try block to handle Error of Try 

Empsalary is a Float field i will try to insert an alphanumeric field in that column

begin try

insert into Employee_ErrorHandling values('Arun','xyz')

end try

begin catch

SELECT 

   ERROR_NUMBER() AS ErrorNumber

   ,ERROR_MESSAGE() AS ErrorMessage;

end catch

Output

ErrorNumber ErrorMessage

8114 Error converting data type varchar to float.

--Remove alphanumeric data change it to float the code will run without any error no. and message

begin try

insert into Employee_ErrorHandling values('Arun','5000')

end try

begin catch

SELECT 

   ERROR_NUMBER() AS ErrorNumber

   ,ERROR_MESSAGE() AS ErrorMessage;

end catch

It's always a best Practise to write you TSQL code using Try and Catch Block so that your code will not stop in between if an error Occurs instead of that it will show some Custom or an Error message.