Ein kürzlicher Beitrag von Yuri (object_id der aktuellen gespeicherten Prozedur ermitteln) hat mich daran erinnert, dass ich schon seit einiger Zeit meinem Unmut über eine Inkonsequenz in SQL Server Ausdruck verleihen wollte.

Mit SQL Server 2005 wurde die Trennung von Benutzern und Schemata vollzogen.Schemas gab es zwar schon früher, aber nun erst wurde es möglich, diese sinnvoll zu nutzen, IMHO. Gleichzeitig wurde in SQL Server 2005 mit TRY...CATCH ein neuer Weg zur strukturierten Fehlerbehandlung eingeführt.Damit konnte man nun endlich seine Fehlerbehandlung in einer Prozedur zentralisieren und vereinheitlichen. Im CATCH Block (oder einer zentralen Fehlerbehandlungsprozedur) kann man nun mit Funktionen wie

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

bequem allerlei hilfreiche Informationen zu einem Fehler erhalten. Eine dieser neu geschaffenen Funktionen war ERROR_PROCEDURE, welche laut Books Online folgende Aufgabe hat:

ERROR_PROCEDURE() gibt den Namen der gespeicherten Prozedur oder des Triggers zurück, in der bzw. in dem der Fehler auftrat.

Und das stimmt auch sogar! Allerdings auch nur den Namen der gespeicherten Prozedur zurück und nicht mehr. Dies kann nun zu unerwarteten Ergebnissen führen, wenn man Schemata verwendet und eine zentrale Fehlerbehandlungsprozedur hat, die man standardmässig im CATCH Block anspricht, sei es, um den Fehler in einer Logging-Tabelle zu speichern, die Fehlermeldung benutzerfreundlicher zu gestalten, etc...

Beispiel:

Zunächst einmal brauchen wir ein neues Schema

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

Dann brauchen wir eine zentrale Fehlerbehandlungsprozedur:

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

Diese Prozedur ist nur ein Basisgerüst. Normalerweise würde in der Prozedur wahrscheinlich @ErrorMessage in eine Tabelle geschrieben und/oder formatiert und wieder ausgegeben usw....

Egal, die obige Prozedur soll

• über ERROR_PROCEDURE() den Namen der Prozedur, welche den Fehler verursacht hat
• über OBJECT_ID(ERROR_PROCEDURE()) die eindeutige ObjectID der Fehlerprozedur
• über OBJECT_SCHEMA(OBJECT_ID(ERROR_PROCEDURE())) das Schema der Fehlerprozedur

zurückgeben.

Nun brauchen wir noch eine Prozedur in dem gerade erstellten neuen 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

Diese Prozedur provoziert einen "Division durch 0" Fehler und leitet die benutzerdefinierte Fehlermeldung in @errmsg an die Fehlerbehandlungsprozedur weiter.

Führt man nun die Prozedur Foo.Bar aus erhält man die folgende Ergebnismenge:

Wie man sehen kann, erhält man noch den Namen der Prozedur zurück, die ObjectID und das ObjectSchema jedoch bereits nicht mehr. Wieso? Nun, OBJECT_ID liefert NULL im Falle eines Fehlers zurück und da weder im dbo Schema noch im Schema der aktuellen Datenbankbenutzers (sofern ein solches überhaupt existiert) eine Prozedur mit dem Namen "Bar" gefunden werden kann, wird ein Fehler ausgelöst und NULL zuückgegeben. Da nun OBJECT_ID NULL ist, liefert zwangsläufig auch die dritte Spalte NULL zurück.

Um den Punkt nochmals zu verdeutlichen erstellen wir noch eine weitere gleichnamige Prozedur, diesmal jedoch im Schema "dbo":

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

Führt man nun diese Prozedur aus, erscheint folgendes:

Alles funktioniert nun wie erwartet und so liefern alle 3 Spalten sinnvolle Daten zurück.

Dem aufmerksamen Leser wird jetzt nicht entgangen sein, dass wir nun 2 gleichnamige Prozeduren (Bar) in verschiedenen Schemas (Foo & dbo) haben. Sollte man vielleicht noch mit den NULL Werten im Aufruf von EXEC Foo.Bar leben können, liefern nun deren erneuter Aufruf folgendes zurück:

Was schlicht und einfach falsch ist, da es nicht dir dbo.Bar Prozedur war, die den Fehler verursacht hat.

Nun kann man natürlich versuchen, um diese Limitation herumzuarbeiten. Zum Beispiel mit:

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

was allerdings nur dann funktioniert, wenn man ausschliessen kann, dass gleichnamige Objekte in verschiedenen Schemata vorkommen. hier das Ergebnis in unserem Falle:

Damit ist einem dann auch nicht wirklich geholfen. Verlässlich funktioniert nur, wenn man die ObjectID der aktuellen Prozedur beim Aufruf an die Fehlerbehandlungsprozedur mit übergibt und dann mit dieser innerhalb der Fehlerbehandlungsprozedur weiterarbeitet:

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

Die Parameterliste der Prozedur wurde erweitert und die @CurrentProcID. Der Output der Prozedur besteht nun aus 4 Spalten. Die ersten beiden Spalten liefern Informationen auf Basis von @CurrentProcID zurück, während die letzten beiden Spalten die Informationen anhand von ERROR_PRORCEDURE() zurückgeben. Erweitern wir nun unsere beiden Prozeduren, so dass diese nun die @@PROCID an die Fehlerbehandlungsprozedur mit übergeben:

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

und führen sie aus, erhalten wir folgendes:

EXEC Foo.Bar;
EXEC dbo.Bar;

Dieses Ergebnis entspricht in beiden Fällen genau den Erwartungen. Allerdings liefern nur die ersten beiden Spalten in beiden Fällen ein korrektes Ergebnis zuück, während die letzten beiden Spalten kein verlässliches Ergebnis liefern.

Es erscheint mir etwas merkwürdig, von "aussen" heraus Daten liefern zu müssen, damit eine Identifizierung eines Objektes bei einem Fehler eindeutig möglich ist. Viel einfacher wäre es, ein Konstrukt wie ERROR_PROCID() zu haben, welches dann den vollständig qualifizierten Objektnamen zurückgibt, wenn man schon ERROR_PROCEDURE() nicht ändern will.

Zu diesem Ganzen gibt es auch ein Connect-Item, über welches man Microsoft wissen lassen kann, ob man diese Feature für wichtig hält oder nicht: Schema not reported in the ERROR_PROCEDURE function

Da mittlerweile einige Jahre seit der Einführung von SQL Server 2005 vergangen sind, erscheint es mir sinnvoller, den Weg über ERROR_PROCID() zu bestreiten, da man davon ausgehen kann, dass eine Aenderung im Verhalten von ERROR_PROCEDURE() mehr oder weniger grosse Probleme mit der Abwärtskompatibilität verursachen dürfte.