Wednesday, 21 March 2018

Exception Handling in SQL Server

Exception Handling in SQL Server
SQL Server provides TRY, CATCH blocks for exception handling. 

BEGIN TRY

/* T-SQL Statements */

END TRY
BEGIN CATCH


- Print Error OR
- Rollback Transaction

END CATCH 

Types of Exceptions

SQL Server contains the following two types of exceptions:
1- System Defined
2- User Defined

System Defined Exception
In a System Defined Exception the exceptions (errors) are generated by the system.

  1. Declare @val1 int;  
  2. Declare @val2 int;  
  3. BEGIN TRY  
  4. Set @val1=8;  
  5. Set @val2=@val1/0; /* Error Occur Here */  
  6. END TRY  
  7. BEGIN CATCH  
  8. Print 'Error Occur that is:'  
  9. Print Error_Message()  
  10. END CATCH  

Output
Error Occur that is:
Divide by zero error encountered

User Defined Exception
This type of exception is user generated, not system generated.

  1. Declare @val1 int;  
  2. Declare @val2 int;  
  3. BEGIN TRY  
  4. Set @val1=8;  
  5. Set @val2=@val1%2;   
  6. if @val1=1  
  7. Print ' Error Not Occur'  
  8. else  
  9. Begin  
  10. Print 'Error Occur';  
  11. Throw 60000,'Number Is Even',5  
  12. End  
  13.   
  14. END TRY  
  15. BEGIN CATCH  
  16. Print 'Error Occur that is:'  
  17. Print Error_Message()  
  18. END CATCH  

Output
Error Occur
Error Occur that is:
Number Is Even

Here 60000 denotes the error number and 5 denotes the state to associate with the message.

The following are system functions and the keyword used within a catch block:

@@ERROR
ERROR_NUMBER()
ERROR_STATE()
ERROR_LINE()
ERROR_MESSAGE()
ERROR_PROCEDURE()
ERROR_SEVERITY()
RAISERROR()
GOTO()

0 comments:

Post a Comment

Topics

ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (9) Web Service (1) XMl (1)