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.
DECLARE @iError INT SELECT 1/0 AS [ForceError] SET @iError = @@error PRINT 'Next Statement' IF @iError > 0 GOTO ErrorHandle SELECT @iError ErrorHandle: PRINT 'ERROR HANDLE / ROLLBACK ETC'
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.
ERROR HANDLE / ROLLBACK ETC
@@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.
SET XACT_ABORT ON DECLARE @iError INT SELECT 1/0 AS [ForceError] SET @iError = @@error PRINT 'Next Statement' IF @iError > 0 GOTO ErrorHandle SELECT @iError ErrorHandle: PRINT 'ERROR HANDLE / ROLLBACK ETC'
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
SET XACT_ABORT ON BEGIN TRY DECLARE @iError INT SELECT 1/0 AS [ForceError] SET @iError = @@error PRINT 'Next Statement' SELECT 'WE ARE IN TRY BLOCK' END TRY BEGIN CATCH PRINT 'ERROR HANDLE / ROLLBACK ETC' SELECT 'WE ARE IN CATCH BLOCK' END 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.
- 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.
BEGIN TRY BEGIN TRAN INSERT INTO #t (ID, Name) VALUES ('NOT INT', 'Prasad Sahoo') PRINT 'Next Statement' SELECT 'WE ARE IN TRY BLOCK' AS [TRY] COMMIT TRAN END TRY BEGIN CATCH SELECT XACT_STATE() as [TRAN_STATE], ERROR_MESSAGE() as ERR_MSG, 'WE ARE IN CATCH BLOCK' AS [CATCH] IF XACT_STATE() <> 1 ROLLBACK TRAN PRINT 'ERROR HANDLE / ROLLBACK ETC' END CATCH
- 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.
BEGIN TRANSACTION SELECT @@trancount AS [1ST LEVEL], XACT_STATE() AS [TRAN STATE] BEGIN TRANSACTION SELECT @@trancount AS [2ND LEVEL] , XACT_STATE() AS [TRAN STATE] PRINT 'AND SO ON' SELECT @@trancount AS [FINAL LEVEL] , XACT_STATE() AS [TRAN STATE] ROLLBACK
- 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.
- 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.
- Return type is INT
- Returns the exact line number at which an error occurred.
- 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.
- 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.
- 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 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
RAISERROR('Error raised by RAISERROR',16,1) PRINT 'After RAISERROR call' -- This will be printed ;THROW 50000, 'Error raised by THROW', 1 PRINT 'After THROW call' -- This will be not printed
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)
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --Error repository table IF OBJECT_ID('ErrorLog') IS NULL BEGIN CREATE TABLE [dbo].[ErrorLog]( [ErrorLogID] bigint IDENTITY(1,1) NOT NULL, [ErrorTime] datetime NOT NULL DEFAULT (GETDATE()), [UserName] nvarchar(255) NOT NULL, [HostName] nvarchar(255) NULL, [ErrorNumber] int NOT NULL, [ErrorSeverity] int NULL, [ErrorState] int NULL, [ErrorProcedure] nvarchar(128) NULL, [ErrorLine] int NULL, [ErrorMessage] VARCHAR(MAX) NOT NULL, CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED ([ErrorLogID] ASC) ) END GO -- uspLogError logs error information in the ErrorLog table about the -- error that caused execution to jump to the CATCH block of a -- TRY...CATCH construct. This should be executed from within the scope -- of a CATCH block otherwise it will return without inserting error -- information. CREATE PROCEDURE [dbo].[usp_LogError] @Error_Number INT = NULL ,@Error_Message VARCHAR(MAX) = NULL ,@Error_Severity INT =NULL ,@Error_State INT = NULL ,@Error_Procedure VARCHAR(200) = NULL ,@Error_Line INT = NULL ,@ErrorLogID [bigint] = 0 OUTPUT AS BEGIN SET NOCOUNT ON; SET @ErrorLogID = 0; BEGIN TRY IF ERROR_NUMBER() IS NULL RETURN; -- Return if inside an uncommittable transaction. -- Data insertion/modification is not allowed when -- a transaction is in an uncommittable state. IF XACT_STATE() = -1 BEGIN PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' + 'Rollback the transaction before executing usp_LogError in order to successfully log error information.'; RETURN; END INSERT [dbo].[ErrorLog] ( [UserName], [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine], [ErrorMessage], [HostName] ) VALUES ( SUSER_SNAME(), COALESCE(@Error_Number,ERROR_NUMBER()), COALESCE(@Error_Severity,ERROR_SEVERITY()), COALESCE(@Error_State,ERROR_STATE()), COALESCE(@Error_Procedure,ERROR_PROCEDURE()), COALESCE(@Error_Line,ERROR_LINE()), COALESCE(@Error_Message,ERROR_MESSAGE()), HOST_NAME() ); -- Pass back the ErrorLogID of the row inserted SET @ErrorLogID = SCOPE_IDENTITY(); END TRY BEGIN CATCH PRINT 'An error occurred in stored procedure usp_LogError: '; END CATCH END;
Sample use of usp_LogError in the catch block
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;