Exception Handling in SQL Server

Like C#, SQL Server also has an exception model to handle exceptions and errors that occur in T-SQL statements. To handle exceptions in SQL Server, SQL Server 2005 has introduced TRY-CATCH statements. We put T-SQL statements in a TRY block and to handle exceptions we write code in CATCH blocks.

The following is the syntax of TRY-CATCH Block:

BEGIN TRY
               // SQL Statements
END TRY
BEGIN CATCH
               //Handle the exception details
               //Control is ed to CATCH block only if there are any exceptions in the TRY block
END CATCH

For example:

ALTER PROCEDURE UpdateEmployeeSal
 @EmpID INT,
 @SalAmt MONEY = 0
AS
BEGIN
 BEGIN TRY
 BEGIN TRANSACTION;
 UPDATE EmployeeMaster
 SET Salary = @SalAmt
 WHERE EmployeeID = @EmpID;
 COMMIT TRANSACTION;
 END TRY
 BEGIN CATCH
 IF @@TRANCOUNT > 0
 ROLLBACK TRANSACTION;
      PRINT 'Error Number  :' + CAST(ERROR_NUMBER() AS VARCHAR)
      PRINT 'Error Severity:' + CAST(ERROR_SEVERITY() AS VARCHAR)
      PRINT 'Error State   :' + CAST(ERROR_STATE() AS VARCHAR)
      PRINT 'Error Line    :' + CAST(ERROR_LINE() AS VARCHAR)
      PRINT 'Error Message :' + ERROR_MESSAGE()
 THROW;
 END CATCH
END;
GO

Error functions in SQL Server

The following is the list of ERROR FUNCTIONS that we can use in the CATCH block to get the details of the error that resulted in transferring the control from the TRY block to the CATCH block.

• ERROR_NUMBER(): Returns the Error Number.
  
  
• ERROR_SEVERITY(): Returns the Severity of the Error.
  
  
• ERROR_STATE(): Returns the State of the Error.
  
  
• ERROR_PROCEDURE(): Returns the name of the SP/UDF in which the error occurred.
  
  
• ERROR_LINE(): Returns the line number of the SQL statement that raised the error.
  
  
• ERROR_MESSAGE(): Returns the error message.

Parts of Error Message

1. ERROR NUMBER

Any error number <= 50000 is a System Defined Message and the ones that are > 50000 are User Defined Messages. SYS.Messages catalog view can be used to retrieve both System and User Defined Messages. We can add a user defined message using sp_addmessage and we can remove it using the system stored procedure sp_dropmessage.

2. ERROR SEVERITY

Error Severity can be between 0-25.

• 0-10: Informational or a warning.
• 11-16: Programming Errors.
• 17-25: Resource / Hardware / OS/ SQL Server Internal Errors.
• 20-25: Terminates the Connection.
• 19-25: Only User with SysAdmin rights can raise error's with this severity.

3. ERROR STATE

The same error can be raised for several different conditions in the code. Each specific condition that raises the error assigns a unique state code. Also the SQL Support team uses it to find the location in the source code where that error is being raised.

4. ERROR PROCEDURE

The name of the Stored Procedure or the function in which the error occurred. It will be blank if it is a normal batch of statements.

5. ERROR LINE

Line number of the statement within a SP, UDF or Batch that triggered the error. It will be 0 if a SP or UDF invoke causes the error.

6. ERROR MESSAGE

Error description detailing the reason for the error.