Exception Handling in SQL Server

An error condition during a program execution is called an exception and the mechanism for resolving such an exception is known as an exception handler. SQL Server provides TRY, CATCH blocks for exception handling. We can put all T-SQL statements into a TRY BLOCK and the code for exception handling can be put into a CATCH block. We can also generate user-defined errors using a THROW block.

Syntax of Exception Handling

Syntax of Exception Handling

BEGIN TRY

/* T-SQL Statements */

END TRY
BEGIN CATCH

- Print Error OR
- Rollback Transaction

END CATCH


In exception handling all T-SQL statements are put into a try block. If all statements execute without any error then everything is OK else control will go to the catch block.

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.

Example

  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:

  1. @@ERROR
  2. ERROR_NUMBER()
  3. ERROR_STATE()
  4. ERROR_LINE()
  5. ERROR_MESSAGE()
  6. ERROR_PROCEDURE()
  7. ERROR_SEVERITY()
  8. RAISERROR()
  9. GOTO()

Now we will see some examples to help understand all these functions and keywords.

First create a table and enter some value into the table as in the following:

  1. Create TABLE Employee  
  2. (  
  3. Emp_IId Int identity(1,1),  
  4. First_Name Nvarchar(MAXNot NUll,  
  5. Last_Name Nvarchar(MAXNot Null,  
  6. Salary Int Not Null check(Salary>20000),  
  7. City Nvarchar(MaxNot Null  
  8. )  
Insert data into Employee.
  1. Select 'Pankaj','Choudhary',25000,'Alwar' Union All  
  2. Select 'Rahul','Prajapat',23000,'Alwar' Union All  
  3. Select 'Sandeep','Jangid',27000,'Alwar' Union All  
  4. Select 'Sanjeev','Baldia',24000,'Alwar' Union All  
  5. Select 'Neeraj','Saini',22000,'Alwar' Union All  
  6. Select 'Narendra','Sharma',23000,'Alwar' Union All  
  7. Select 'Divyanshu','Gupta',25000,'Alwar'   
Now execute a select command.
  1. Select * From Employee  
execute select command

Example 1: (@@ERROR)

@@ERROR return the error number for last executed T-SQL statements. It returns 0 if the previous Transact-SQL statement encountered no errors else return an error number.
  1. Update Employee set Salary=19000 Where Emp_IID=5  
  2. IF @@ERROR = 547  
  3. PRINT 'A check constraint violation occurred.';  
Output:

    Msg 547, Level 16, State 0, Line 1

    The UPDATE statement conflicted with the CHECK constraint "CK__Employee__Salary__68487DD7". The conflict occurred in database "Home_Management", table "dbo.Employee", column 'Salary'.

The statement has been terminated.

A check constraint violation occurred.

Example 2 (ERROR_NUMBER)

ERROR_NUMBER() returns the error number that caused the error. It returns zero if called outside the catch block.

  1. BEGIN TRY  
  2.   
  3. Update Employee set Salary=19000 Where Emp_IID=5  
  4. END TRY  
  5. BEGIN CATCH  
  6. SELECT ERROR_NUMBER() AS ErrorNumber;  
  7. END CATCH;  
  8. GO  
Output

catch block
Now a question develops of what is diff @@ERROR and ERROR_NUMBER. Let me explain.
  1. ERROR_NUMBER can only be used in a catch block, outside a catch block it returns Null but @@ERROR can be used inside or outside the catch block (see Example 1).

  2. ERROR_NUMBER is a contrast to @@ERROR, that only returns the error number in the statement immediately after the one that causes an error, or the first statement of a CATCH block.

Now we will see an example and observe the differences between them.

  1. BEGIN TRY  
  2.   
  3. Update Employee set Salary=19000 Where Emp_IID=5  
  4. END TRY  
  5. BEGIN CATCH  
  6.   
  7. SELECT ERROR_NUMBER() AS ErrorNumber;  
  8. print @@ERROR  
  9. END CATCH;  
  10. GO  
Output

take to example
  1. BEGIN TRY  
  2.   
  3. Update Employee set Salary=19000 Where Emp_IID=5  
  4. END TRY  
  5. BEGIN CATCH  
  6. print @@ERROR  
  7. SELECT ERROR_NUMBER() AS ErrorNumber;  
  8.   
  9. END CATCH;  
  10. GO  
Output

error that caused
Example 3 (ERROR_MESSAGE)

ERROR_MESSAGE returns the message text of the error that caused the error. The return type of ERROR_MESSAGE is nvarchar(4000).
  1. BEGIN TRY  
  2.   
  3. Update Employee set Salary=19000 Where Emp_IID=5  
  4. END TRY  
  5. BEGIN CATCH  
  6. SELECT ERROR_MESSAGE() AS ErrorMsg;  
  7.   
  8. END CATCH;  
  9. GO  
Output

    The UPDATE statement conflicted with the CHECK constraint "CK__Employee__Salary__68487DD7". The conflict occurred in database "Home_Management", table "dbo.Employee", column 'Salary'.

Example 4 ( ERROR_STATE)

ERROR_STATE returns the state number of the error. The return type of ERROR_STATE is INT.

  1. BEGIN TRY  
  2.   
  3. SELECT SALARY + First_Name From Employee Where Emp_IID=5  
  4. END TRY  
  5. BEGIN CATCH  
  6. SELECT ERROR_STATE() AS ErrorState , ERROR_MESSAGE() ErrorMsg ;  
  7. END CATCH;  
  8. GO  
Output

INT

Example 5 (ERROR_LINE)

ERROR_LINE returns the line number at which an error occurred. The return type of ERROR_LINE is INT.
  1. BEGIN TRY  
  2. SELECT SALARY + First_Name From Employee Where Emp_IID=5  
  3. END TRY  
  4. BEGIN CATCH  
  5. SELECT ERROR_STATE() AS ErrorLine;  
  6. END CATCH;  
  7. GO  
Output

ERROR LINE
Example 6 (ERROR_PROCEDURE)

ERROR_PROCEDURE returns the name of the Stored Procedure or trigger of where an error occurred. The return type of ERROR_PROCEDURE is nvarchar(128).

Return value

Return value returns the Stored Procedure Name if an error occurs in a Stored Procedure or trigger and the catch block is called.

It returns NULL if the error did not occur within a Stored Procedure or trigger or it isb called outside the scope of a CATCH block.

First we create a Stored Procedure.
  1. Create Procedure My_Proc  
  2. AS  
  3. begin  
  4. BEGIN TRY  
  5. SELECT SALARY + First_Name From Employee Where Emp_IID=5  
  6. END TRY  
  7. BEGIN CATCH  
  8. SELECT ERROR_PROCEDURE() AS ProcName;  
  9. END CATCH;  
  10. END  
Now execute this Stored Procedure.
  1. Exec My_Proc  
Output

execute this Stored Procedure

Example 7 (ERROR_SEVERITY)

ERROR_SEVERITY returns the severity of the error. The return type of ERROR_SEVERITY is INT.
  1. BEGIN TRY  
  2. SELECT SALARY + First_Name From Employee Where Emp_IID=5  
  3. END TRY  
  4. BEGIN CATCH  
  5. SELECT ERROR_SEVERITY() AS ErrorSeverity;  
  6. END CATCH;  
Output

ErrorSeverity

The severity level of an error message provides an indication of the type of problem that Microsoft® SQL Server encountered. In the preceding example the Severity Level is 16. That means that the error can be removed by the user.

Some important severity levels are:

13 Indicates transaction deadlock errors.
14 Indicates security-related errors, such as permission denied.
15 Indicates syntax errors in the Transact-SQL command.
16 Indicates general errors that can be corrected by the user.

Example 8 (RAISERROR)

RAISEERROR is used to generate an error message and initiates error processing for the session.

  1. BEGIN TRY  
  2. SELECT SALARY + First_Name From Employee Where Emp_IID=5  
  3. END TRY  
  4. BEGIN CATCH  
  5. RAISERROR(N'An Error Is Occur',16,3);  
  6. END CATCH;  
Output
An Error Is Occur
In RAISERROR(N'An Error Is Occur',16,3) the first argument represents the error messagethe , second argument represents the Severity Level and the last argument represents the Error State.

Example 9 (GOTO)

GOTO causes a jump to a specific step or statement. It alters the flow of execution to a label. We declare some labels in batch and alter we can move at a specific label. GOTO can exist within a conditional control-of-flow statements, statement blocks, or procedures, but it cannot go to a label outside the batch. GOTO cannot be used to jump into a TRY or CATCH scope.
  1. Declare @Var Int;  
  2. Set @Var=1  
  3. Print 'Goto exercise'  
  4. If @Var%2=0  
  5. GOTO Label1;  
  6. else  
  7. GOTO Label2;  
  8. Set @Var=@Var+1;  
  9. Label1:  
  10. Print 'Var Is Odd'  
  11. Label2:  
  12. Print 'Var Is Even'  
Output

    Goto exercise
    Var Is Even

Example 10

  1. BEGIN TRY  
  2. SELECT SALARY + First_Name From Employee Where Emp_IID=5  
  3. END TRY  
  4. BEGIN CATCH  
  5. SELECT ERROR_STATE() AS Error_Stat,ERROR_SEVERITY() AS ErrorSeverity, ERROR_LINE() as ErrorLine, ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMsg;  
  6. END CATCH;  
Output

ErrorNumber

Exercise 11 (Transaction Management)

Exception handling is mainly used for Transaction Management. Let us see an example.
  1. Begin Transaction Trans  
  2.    Begin Try  
  3.       Delete From Employee Where Employee.Emp_IID<3  
  4.       Update Employee Set Employee.First_Name='Pankaj kumar' Where Employee.Emp_IID='6th' /* Error Will Occur Here */  
  5.       If @@TranCount>0  
  6.       begin Commit Transaction Trans  
  7.    End  
  8. End Try  
  9. Begin Catch  
  10.    if @@TranCount>0  
  11.    Print 'Error Is Occur in Transaction'  
  12.    begin Rollback Transaction Trans  
  13. End  
  14. End Catch  
  15.   
  16. Select * From Employee  
Output

Output

When to use Exception Handling:
  1. In Transaction Management to Rollback the transaction.
  2. While using cursors in SQL Server.
  3. When implementing a DML Query (insert, update or delete) for for an checking the error and to handle it.