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