ERROR_PROCEDURE does not return a schema name

A recent blog entry I read reminded me again that I wanted to rant about an issue in SQL Server for quite some time now…

SQL Server 2005 introduced the separation between user and schema. Though schemata already existed  before SQL Server 2005, they really became usable with this version, imho. At the same time  TRY…CATCH was a new way for structured error handling introduced. And so it finally became possible amongst other things to centralise and standardise one’s own error handling. Inside the CATCH block one could possibly call a common procedure or directly use functions like the following ones:

• ERROR_NUMBER()
• ERROR_SEVERITY()
• ERROR_STATE()
• ERROR_PROCEDURE()
• ERROR_LINE()
• ERROR_MESSAGE()

All these new functions can be used to conveniently collect all kinds of useful information about an error. One of these newly created functions was ERROR_PROCEDURE(), which according to Books Online is supposed to:

“Returns the name of the stored procedure or trigger where an error occurred that caused the CATCH block of a TRY…CATCH construct to be run.”

And that is exactly what it does. But admittedly it just returns only the name of the stored procedure and nothing else. This however can yield surprising and unexpected results.

Example:

First of all, we need a new schema:

IF SCHEMA_ID('Foo') IS NULL
    EXEC ('CREATE SCHEMA Foo AUTHORIZATION dbo');
GO

Next we need a central error handling procedure:

PRINT 'dbo.CatchAllError (create procedure)';
GO
IF OBJECT_ID('dbo.CatchAllError') IS NOT NULL
    DROP PROCEDURE dbo.CatchAllError;
GO
CREATE PROCEDURE dbo.CatchAllError
    @ErrorMessage varchar(2000)
AS
BEGIN TRY
 SELECT
  ERROR_PROCEDURE() AS NameOfProcedure,
  OBJECT_ID(ERROR_PROCEDURE()) AS ObjectID,
  OBJECT_SCHEMA_NAME(OBJECT_ID(ERROR_PROCEDURE())) AS ObjectSchema
END TRY
BEGIN CATCH
END CATCH
GO

Of course is this procedure nothing more than a basic skeleton. Typically you would want to save @ErrorMessage to a table and/or format it and return it to the client. As you can see is this procedure created in the dbo schema. The dbo schema is just chosen for convenience, because it already exists. It could actually be any other schema that you want to share or any other schema name you could think of. The point just is that such a common shared procedure resides in a different schema than most of the other procedures which call the error handling routine.

Anyway, the above shown procedure is supposed to

• return the name of the procedure that caused the error via by calling ERROR_PROCEDURE()
• return the unique objectid of the procedure that caused the error via OBJECT_ID(ERROR_PROCEDURE())
• return the schema of the procedure that caused the error via OBJECT_SCHEMA(OBJECT_ID(ERROR_PROCEDURE()))

Now we need another stored procedure in the newly created schema:

PRINT 'Foo.Bar (create procedure)';
GO
IF OBJECT_ID('Foo.Bar') IS NOT NULL
    DROP PROCEDURE Foo.Bar;
GO
CREATE PROCEDURE Foo.Bar
AS

DECLARE @errmsg varchar(2000);

BEGIN TRY
    SELECT @errmsg = 'ERROR: Something stupid happened';
    SELECT 1/0;
END TRY
BEGIN CATCH
    EXEC dbo.CatchAllError @errmsg;
END CATCH
GO

This procedure provokes a “Division by 0” error and passes the user-defined error message in @errmsg to the error handling procedure. If we now execute Foo.Bar, this is the result we get:

NameOfProcedure	ObjectID	ObjectSchema
Bar	NULL	NULL

As you can see, you get the name of the procedure back, but nothing else. Why? Well, OBJECT_ID returns NULL in case of an error and since there is no procedure with the name “Bar” in the current user schema (if such a schema exists at all) nor in the dbo schema, an error occurs and NULL is returned. Now, since OBJECT_ID() returns NULL, the third column must return NULL as well. To demonstrate the point, let’s create another procedure with the same name, but this time in the dbo schema:

PRINT 'dbo.Bar (create procedure)';
GO
IF OBJECT_ID('dbo.Bar') IS NOT NULL
    DROP PROCEDURE dbo.Bar;
GO
CREATE PROCEDURE dbo.Bar
AS

DECLARE @errmsg varchar(2000);

BEGIN TRY
    SELECT @errmsg = 'ERROR: Something stupid happened';
    SELECT 1/0;
END TRY
BEGIN CATCH
    EXEC dbo.CatchAllError @errmsg;
END CATCH
GO

When you now execute this second procedure, you’ll get something like this:

NameOfProcedure	ObjectID	ObjectSchema
Bar	179583778	dbo

Everything is working as expected and the result set is correct. The interested reader however now realizes that there are two procedures of the same name (Bar) in 2 different schemata (Foo & dbo). If you could have accepted to live with the NULLs in the first call of Foo.Bar a second call now returns:

NameOfProcedure	ObjectID	ObjectSchema
Bar	179583778	dbo

This now is simply wrong, because it wasn’t dbo.Bar which caused the error!

Of course, you can now try to work around this limitation. For example, like this:

    SELECT
        SCHEMA_NAME(O.schema_id)
    FROM
        sys.objects O
    WHERE
        O.name = ERROR_PROCEDURE();

However, this only works reliably when you can rule out that objects of the same name exist in different schemata. If that is not the case, you will get a result set like the one in my case:

dbo

Foo

So this does not get you very far. About the only reliable way we found so far is to pass the objectid of the procedure that caused the error to the error handling procedure and work on this objectid later on. So, an updated version of the common error handling procedure might look like this:

PRINT 'dbo.CatchAllError (create procedure)';
GO
IF OBJECT_ID('dbo.CatchAllError') IS NOT NULL
    DROP PROCEDURE dbo.CatchAllError;
GO
CREATE PROCEDURE dbo.CatchAllError
    @CurrentProcID int,
    @ErrorMessage varchar(2000)
AS
BEGIN TRY
    SELECT
        OBJECT_NAME(@CurrentProcID) AS NameCurrentProcID,
        OBJECT_SCHEMA_NAME(@CurrentPROCID) AS SchemaCurrentProcID, 
        ERROR_PROCEDURE() AS NameErrorProcedure,
        OBJECT_SCHEMA_NAME(OBJECT_ID(ERROR_PROCEDURE())) 
         AS SchemaErrorProcedure
END TRY
BEGIN CATCH
END CATCH
GO

The parameter list has been expanded for the @CurrentProcID. The output of the procedure consists now of 4 columns. The first 2 columns return information based on @CurrentProcID, while the second 2 columns give information based on ERROR_PROCEDURE(). Now we need to expand the two stored procedures to pass @@PROCID to the central error handling procedure:

PRINT 'Foo.Bar (create procedure)';
GO
IF OBJECT_ID('Foo.Bar') IS NOT NULL
    DROP PROCEDURE Foo.Bar;
GO
CREATE PROCEDURE Foo.Bar
AS

DECLARE @errmsg varchar(2000);

BEGIN TRY
    SELECT @errmsg = 'ERROR: Something stupid happened';
    SELECT 1/0;
END TRY
BEGIN CATCH
    EXEC dbo.CatchAllError @@PROCID , @errmsg;
END CATCH
GO

PRINT 'dbo.Bar (create procedure)';
GO
IF OBJECT_ID('dbo.Bar') IS NOT NULL
    DROP PROCEDURE dbo.Bar;
GO
CREATE PROCEDURE dbo.Bar
AS

DECLARE @errmsg varchar(2000);

BEGIN TRY
    SELECT @errmsg = 'ERROR: Something stupid happened';
    SELECT 1/0;
END TRY
BEGIN CATCH
    EXEC dbo.CatchAllError @@PROCID, @errmsg;
END CATCH
GO

Now we execute both procedures again:

EXEC Foo.Bar;
EXEC dbo.Bar;

NameCurrentProcID	SchemaCurrentProcID	NameErrorProcedure	SchemaErrorProcedure
Bar	Foo	Bar	dbo

NameCurrentProcID	SchemaCurrentProcID	NameErrorProcedure	SchemaErrorProcedure
Bar	dbo	Bar	dbo

The results meet the expectations. Only the first 2 columns deliver a correct result set consistently for both calls, while the second 2 columns are not really reliable.

It seems to be a bit strange to feed information to SQL Server for the unique identification of an object as we do in case of passing the @@PROCID from the CATCH block of the procedure which caused the error to a common procedure. But even if we would want to identify the procedure in the CATCH block of the procedure itself, we would have to provide this additional information.

It’s been a few years now since SQL Server 2005 was released, so it is fairly likely to assume that the behaviour of ERROR_PROCEDURE() cannot be changed without breaking more or less applications that rely on the current behaviour. It should have been changed ideally before SQL Server 2005 was released or shortly thereafter. Now it is just too late to change the existing implementation. However, it cannot be overly difficult to provide a new function like ERROR_SCHEMA() that returns the schema of the procedure that caused the error. Though this seems to be redundant to me, since SQL Server already provides the OBJECT_SCHEMA_NAME() function that resolves the schema name given an objectid. I guess I would prefer to have a function like ERROR_PROCID() that is equivalent in functionality to @@PROCID and that returns the objectid of the procedure that caused an error. If you have the feeling that this all is a feature that needs to be changed and/or added to SQL Server, you can vote for it in this Connect item. Apparently it is not really high up in the priority list of things to be fixed.