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

Continues…

Pages: 1 2 3




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |