Exception Handling In SQL Server

Try and catch blocks are very helpful to find out errors coming in .NET application. In database side, errors also comes, sometimes these errors may be dangerous for our system. Implementing Error handling in SQL Server is same as in C# language. In this article I am going to show you, how you can handle exception in back-end. For this we will put our SQL statements in try block and in catch block we put statements for error block. When error occurs in statement, it automatically jumps into catch block.

Syntax of Try catch block

BEGIN TRY
   --SQL Statements
   END TRY
BEGIN CATCH
   --Statements for Catch block when error occurs
END CATCH

Example 1

BEGIN TRY
   SELECT 2/0
   END TRY
BEGIN CATCH
   PRINT 'Error in your statement'
END CATCH

You can see in the preceding example, BEGIN TRY and I wrote Divide by zero statement. When error occurs it automatically jumps to BEGIN CATCH part, and in message section you will get a print message "Error in your statement". Actually,  Divide by zero technique is easy to implement is easy because we know error will definitely come into my programme.

Example 2

BEGIN TRY
   SELECT 2/0
   END TRY
BEGIN CATCH
   SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO

In the above example, you can see, in catch block here are some error functions. So first let's understand error functions in catch block.

 

1. ERROR_NUMBER(): It returns the error number.
   
   
2. ERROR_LINE(): This function returns line number, where error occur. In above code you can see our error comes in line number 2.
   
   
3. ERROR_SEVERITY(): This returns Severity of Error.
   
   
4. ERROR_STATE(): State number of Error.
   
   
5. ERROR_PROCEDURE(): If error comes from any procedure, it shows the name of stored procedure.
   
   
6. ERROR_MESSAGE(): This returns an error message.

That’s it! I hope you enjoyed this article. For any query please mention your comments.