Post
Ask Question

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

  1. BEGIN TRY  
  2.    --SQL Statements  
  3.    END TRY  
  4. BEGIN CATCH  
  5.    --Statements for Catch block when error occurs  
  6. END CATCH  
Example 1
  1. BEGIN TRY  
  2.    SELECT 2/0  
  3.    END TRY  
  4. BEGIN CATCH  
  5.    PRINT 'Error in your statement'  
  6. 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
  1. BEGIN TRY  
  2.    SELECT 2/0  
  3.    END TRY  
  4. BEGIN CATCH  
  5.    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure,  
  6. ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage  
  7. END CATCH  
  8. GO  
output

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.


Similar Articles