Error Handler for T-SQL

E. Sommarskog is the important source on this subject with a set of supremely readable articles here http://www.sommarskog.se/error-handling-I.html – covering up to Sql Server 2005.

ERROR_HandleCaughtError stored procedure

This is  ES’s error handler in a form that suits me: I usually want to add some additional information to aid debugging – e.g. the values of parameters.

  • I seem to be thinking of an “error” schema with the prefix I’ve given it.  Probably dbo is the appropriate location (since little else would ideally end up there).
  • I think I have solved the question of whether the LOG option of RAISERROR is always allowed; I’ll update this post.
  • I’ve gone with ES’s 5555 return value.   Mostly I don’t use the return value, but I’ve yet to form a complete stance on them.  I wouldn’t trust them to indicate errors, largely because of the ambivalence with which they are treated.
  • I am no longer certain why I added Execute As Caller.  It might be important if I were capturing the login running the procedure (and indeed, that might be what appears in the LOG).  On the other hand, it might expose the procedure to the vagaries of Caller’s permissions.  Again, I will confirm and update the post.
CREATE PROCEDURE [dbo].[ERROR_HandleCaughtError] 

        @DebugInfo varchar(2000) = NULL

/*
        2013-01-06

        Reports details of an error to user.
        Logs error to SQL Server Log.

        IN
                @DebugInfo varchar(200) - optional.  Additional 
                                          debug information to append to error message.

	VERSION
		2014-08-19 includes database name.

*/
WITH EXECUTE AS CALLER 
AS
DECLARE @errmsg   nvarchar(2048),
        @severity tinyint,
        @state    tinyint,
        @errno    int,
        @proc     sysname,
        @lineno   int,
		@dbname   varchar(50)

SELECT @errmsg = error_message(), @severity = error_severity(),  
       @state  = error_state(), @errno = error_number(),
       @proc   = error_procedure(), @lineno = error_line(),
	   @dbname = db_name()

IF @errmsg NOT LIKE N'***%'                                         
        BEGIN 
                -- This is the first time the error has been handled in the stack.
                SELECT @errmsg = '*** Proc. ' + @dbname + '..' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                                        ', Ln. ' + ltrim(str(@lineno)) + '. Err. ' + 
                                        ltrim(str(@errno)) + ': ' + @errmsg +
                                        ISNULL(' [' + @DebugInfo + ']', '')
                RAISERROR(@errmsg, @severity, @state) WITH LOG
        END    
ELSE
        BEGIN     
          -- The error information has already been concatenated; rethrow error. No logging required.
           RAISERROR(@errmsg, @severity, @state)

        END

Implementing the Error Handler

Here is a pattern showing the error handler in use in an outline stored procedure.  Copy and Paste.

CREATE PROCEDURE schema.StoredProcedure_Action

        /*
                This is what it does.

                PARAMETERS

                RETURNS

                0/0/0000  Initials : created        
        */

AS

SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @DebugInfo varchar(2000)

BEGIN TRY

        SELECT @DebugInfo = 'int_parameter=' + ISNULL(CONVERT(varchar(8), @int_parameter), 'NULL') +
                           ' date_parameter=' + ISNULL(CONVERT(varchar(20), @date_parameter, 120), 'NULL');

        -- Raise errors for any parameters that must not be Null.
        IF (@int_parameter IS NULL) RAISERROR('@int_parameter is required', 16, 1)
        -- Procedure Actions Here --

END TRY
BEGIN CATCH
        EXECUTE dbo.ERROR_HandleCaughtError @DebugInfo
        RETURN 5555
END CATCH

 Discussion

All exceptions are re-thrown and take the error number of 50000.   Sometimes I would prefer to raise more specific errors to help the application using the procedure – e.g. differentiating between invalid parameters (which could be corrected by the application) and invalid data like a missing lookup value (which couldn’t).     Custom error numbers have to be added to sys.messages: it allows you to specify a custom formatted error message (good), but the custom entries would apply to all databases on the instance, and I don’t care to take that step just yet. A universal set of conventionalised custom errors would be the answer (which ones would you need?), but I haven’t devised one yet, and I have found little evidence of anyone else using them yet.   What would happen if you loaded a third party database that defined its own conflicting custom errors?

 Revisions

1. Aug 2014.   Knowing the database that the error came from is undeniably useful when trawling logs.  Added to this version.  I haven’t yet found a way of returning the schema of the error procedure.

,

no comment

Sorry, comments closed.