Error handling in SQL Server – Complete Overview

For a developer it is very important to get familiar and proficient in the error handling part; no matter in which language you code, better error handling always gives flexibility to your code. You should always consider implementing error handling methods in your T-SQL routines. SQL Server does a pretty good job by letting us know about the error as soon as it encounters any, now it’s our job to handle and manage those errors properly as per our need and per the standard.

There are basically two types of error handling in SQL Server.

1. Unstructured Error Handling.
2. Structured Error Handling.

We will be spending most of our time here discussing on structured error handling and related functions because it’s an advised and best method to handle errors. Also, we will have a glance on our legacy unstructured error handling method.

1. Unstructured Error Handling:

Earlier in legacy system, people used to handle errors by using @@ERROR function after every individual statements to get the error number then GOTO clause to centralize it or do the action on error occurrence.

Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.
Next Statement
ERROR HANDLE / ROLLBACK ETC

Problems:
@@ERROR function is a global variable and it does reset to a new number on report of error to the last executed line of code though to avoid that problem we used another variable to keep the error consistent. Another major problem is centralizing it and having custom code executed on error occurrence, it is also very difficult to capture and log the errors for future reference.

2. Structured Error Handling:

TRY..CATCH was introduced with SQL Server 2005 version. This the best method so far to handle errors in structured way. Before going ahead, let’s check out some functions useful in TRY..CATCH block.

SET XACT_ABORT ON

  • It is an independent function to abort the entire batch if any error occurs. It causes any transactions to rollback having severity level > 10.
  • We can’t trap error number or capture any error information after error occurrence because it causes the entire batch to abort immediately eliminating next statement to execute. Hence it does not give error handling capability unless we use inside TRY..CATCH.

Let’s execute the same code that we demoed in the first step of unstructured way of error handling.

Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

Check the  difference.. The next statements are eliminated and did not get executed after the error occurrence, not even PRINT statement got executed.

Now let’s use XACT_ABORT with TRY..CATCH

Now you will see the error triggered the control to CATCH section and we are successfully able to execute our custom PRINT and SELECT statement.

XACT_STATE()

  • It is a great function to tell us about a transaction in the batch.
  • 0 = There is no open transaction available.
  • 1 = An open transaction is available and can be successfully committed or rolled back.
  • -1 = An open un-committable transaction exists; which cannot be committed due to a fatal error.

 @@TRANCOUNT

  • This fulfills the same purpose like XACT_STATE but with couple of differences.
  • If two transactions called one after another in a batch then @@TRANCOUNT returns 2 in/after the second transaction scope and gives 1 inside the first transaction before the second transaction got executed. To make long story short it increases by 1 after every transaction.
  • We can determine transaction nesting level by using @@TRANCOUNT , which is not possible in XACT_STATE case.

ERROR_MESSAGE()

  • Predefined system error messages can be queried from a system catalog view named sys.sysmessages on basis of error number.
  • The messages are 255 Unicode character long.

ERROR_SEVERITY()

  • This is very important to understand. It is defined from 0 to 25
  • 0 – 10 are informal messages
  • 11 – 16 are actual errors.
  • 16 – 19 are automatically logged in SQL Server and system event viewer.
  • 20 – 25 are considered fatal error making the connection terminated. sysadmin group can only raise this type of severity error manually WITH LOG option.

ERROR_LINE()

  • Return type is INT
  • Returns the exact line number at which an error occurred.

ERROR_PROCEDURE()

  • Returns NVARCHAR(128)
  • Returns the routine name Procedure or Trigger when error occurs.
  • Returns NULL to the CATCH block , if you have used dynamic sql inside the stored procedure.
  • A little work around on this can be found here.

ERROR_STATE()

  • This returns an integer value up to max 127.
  • Some error messages can be raised at multiple points in the code for the Microsoft SQL Server Database Engine. For example, an “1105” error can be raised for several different conditions. Each specific condition that raises the error assigns a unique state code.

RAISERROR()

  • To generate an error manually by supplying parameters to the RAISERROR function.
  • RAISERROR(‘This error is raised manually’,11,1)
  • This triggers the control to the catch block only if the error severity level is 11 and higher. Below 11 are considered as informational message.
  • Know more

THROW

  • THROW was introduced with SQL Server 2012 and is new way to handle errors in more efficient manner. It works same like RAISERROR but with some extra feature.
  • THROW does not require parenthesis to delimit parameters.
  • THROW raises error with severity level 16 by default.
  • It requires error number, message and state as parameter.
  • THROW requires a statement terminator ” ; “ at beginning or at the end of last statement before THROW.
  • THROW always terminates the batch and does not execute next the lines of code unlike RAISERROR.
  • THROW triggers the control to the CATCH block if defined in TRY.
  • THROW alone (without parameter) can be used in the CATCH block to re-raise the same error to the client.
  • Know more

usp_LogError (This is a custom sp created to log errors into a table from calling stored procedures, it can be called in the CATCH section of the stored procedures)

Sample use of usp_LogError in the catch block

BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
–IF XACT_STATE() != 0 ROLLBACK TRANSACTION
–IF @@ERROR !=0 ROLLBACK TRANSACTION

DECLARE @Error_Procedure NVARCHAR(255)
SET @Error_Procedure = OBJECT_NAME(@@PROCID) — To handle NULL proc name
EXECUTE [dbo].[usp_LogError] @Error_Procedure = @Error_Procedure;
END CATCH;

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: