ERROR_PROCEDURE does not return a schema name

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