Error Handler for T-SQL

This is the error handler created E. Sommarskog, with some slight alterations by me.  See

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


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

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

		2014-08-19 includes database name.

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'***%'                                         
                -- 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
          -- The error information has already been concatenated; rethrow error. No logging required.
           RAISERROR(@errmsg, @severity, @state)


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.



                0/0/0000  Initials : created        


DECLARE @DebugInfo varchar(2000)


        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 --

        EXECUTE dbo.ERROR_HandleCaughtError @DebugInfo
        RETURN 5555


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 look like a feature that just hasn’t caught on.  What would happen if you loaded a third party database that defined its own conflicting custom errors?


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.


Conversion to INT failed where no conversion expected

I have twice now been wrong-footed by this SQL Server error message:

“Microsoft SQL Server Native Client 10.0” Hresult: 0x80040E07 Description: “Conversion failed when converting the varchar value ‘abcd123’ to data type int.”.

The statement causing this distress was:

ISNULL(View1.long_nameView1.short_nameAS output_column

Reasonably, ISNULL will expect both arguments to be the same type will attempt conversion if they aren’t.   But in this example both columns long_name and short_name are the same: varchar – aren’t they?

Well, no they aren’t actually.   View1 was hiding some changes that were made to the source table:  long_name was dropped, and to keep things working View1 merely aliased NULL as long_name.     With no other clue, SQL Server interprets the literal NULL as type int, hence the attempted conversion.

T-SQL Pattern: No un-cast literals in views

It’s necessary for numeric literals, fairly common-sense for string literals, but less natural for literal NULL to be cast to the appropriate data type – though a moment’s thought makes it clear why it must.

           NULL as short_name,  -- wrong : what data type will it be? 
           CAST(NULL AS varchar(50)) as long_name  -- the correct pattern

The first line should stand out as unacceptably vague about the data type.