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()