Using TRY CATCH in SQL Server to Handling Exception

Here I will explain that how to handle exceptions or exception handling in SQL using try catch block  in SQL Server.

Try and catch block is very similar to what we use in other.net languages.

For handling exceptions in SQL Server we can use TRY CATCH blocks. To use TRY CATCH blocks in stored procedure we have to write the query like as below

TRY CATCH Block Syntax

BEGIN TRY

--Write Your Query for which we need to do the error handaling

END TRY

BEGIN CATCH 

--Write code for handle error while occure during query excection in try block 

END CATCH 

In TRY block we need to write our query and in CATCH block we need to
write code to handle error occur during query execution.
In our SQL query if error occurs automatically it will move to CATCH block in that we can handle error.
For handling error messages we have some defined Error Functions in CATCH block those are


1. ERROR_LINE() - function will return error line number of SQL query which cause to occure error.

2. ERROR_NUMBER() -  function will return error number which is unique and assigned to it.

3. ERROR_SEVERITY() - function will return severity of error which indicates how serious the error is. The values are between 1 and 25.

4. ERROR_STATE() - function will return state number of error message which cause to occure error.

5. ERROR_PROCEDURE() - function will return name of the procedure where an error occurred.

6. ERROR_MESSAGE() - function will return the complete error message which cause to ouccer error.

Check below query for handling errors in stored procedure

BEGIN TRY
   -- ocure error, as UserID is an IDENTITY column
   -- we can't specify a value for this column.
   INSERT INTO tbl_user(UserID, UserName)
   VALUES(1, 'Test')
END TRY 
BEGIN CATCH 
   SELECT ErrorNumber = ERROR_NUMBER(),
   ErrorSeverity = ERROR_SEVERITY(), ErrorState = ERROR_STATE()
   ErrorProcedure = ERROR_PROCEDURE(), ErrorLine =
   ERROR_LINE(), ErrorMessage = ERROR_MESSAGE()
END CATCH

 If we run above query then we will get output like as shown below