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




Array

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 |