T-SQL Error Handling

In diesem Artikel möchten wir Ihnen den Einstieg in das Thema SQL Server Error Handling näher bringen. Wir stellen Ihnen grundlegende Muster vor, die Sie für Ihren Code verwenden können.

Sinn und Zweck der Fehlerbehandlung

Warum benötigen wir Error Handling für unseren Code? Dafür gibt es diverse Gründe. In vielen Applikationen werden Benutzereingaben validiert und die Benutzer über etwaige Fehler informiert. Diese Benutzerfehler sind erwartete Fehler. Wir müssen jedoch auch unerwartete Fehler behandeln, d.h. Fehler die auftreten, weil wir beispielsweise beim Schreiben unseres Codes etwas übersehen haben.

Eine einfache Strategie besteht darin, die Ausführung abzubrechen oder zumindest zu einem Punkt zurückzukehren, an dem wir wissen, dass wir noch die Kontrolle über die Ausführung haben. Eigentlich ist es ziemlich fahrlässig, einen unvorhergesehenen Fehler zu ignorieren, da hierbei schwerwiegende Folgen auftreten können: Anwendern können falsche Informationen präsentiert werden, oder schlimmer noch: der gesamte Datenstand in unserer Datenbank wird verfälscht.

In unserem Datenbanksystem erwarten wir, dass Aktualisierungen wie INSERT, UPDATE oder DELETE atomar sind. Besteht die Aufgabe beispielsweise darin, Geld von einem Bankkonto auf ein anderes Bankkonto zu transferieren, soll diese Transaktion entweder vollständig oder gar nicht ablaufen. Tabellen die den Kontostand dokumentieren, müssen ebenso aktualisiert werden wie die Tabelle, die eine Transaktionsübersicht bereitstellt. Ein Fehler oder eine Unterbrechung die dazu führt, dass Geld auf das Empfängerkonto gebucht wird, ohne dass es vom anderen Konto abgehoben wird, ist nicht akzeptabel.

Aus diesem Grund geht es in einer Datenbankanwendung bei der Fehlerbehandlung auch um die Behandlung von Transaktionen. In unserem Beispiel müsste der gesamte Buchungsvorgang also zwischen einen BEGIN TRANSACTION und COMMIT TRANSACTION gepackt, bzw. im Fehlerfall zurückgerollt werden.

Essentielle Kommandos

Wir starten, indem wir einen Blick auf die wichtigsten Kommandos werfen, die für eine Fehlerbehandlung in T-SQL notwendig sind.

`TRY-CATCH`

Wie man es auch aus anderen Programmiersprachen kennt, gibt es auch in T-SQL einen TRY-CATCH Block.

BEGIN TRY
  <hier steht dein Code>
END TRY
BEGIN CATCH
  <Fehlerbehandlung>
END CATCH

Tritt ein Fehler im TRY Block auf, wird die Verarbeitung im CATCH Block ausgelöst. Normalerweise rollt CATCH jede offene Transaktion zurück und meldet den Fehler, damit das aufrufende Client-Programm versteht, dass etwas schief gelaufen ist.

Schauen wir uns hierzu ein simples Beispiel an:

BEGIN TRY
    DECLARE @x int
    SELECT @x = 1/0
    PRINT N'Die Berechnung wurde abgeschlossen'
END TRY
BEGIN CATCH
    PRINT N'Fehler: ' + error_message()
END CATCH

Die vorherige Abfrage erzeugt den Output:

Fehler: Divide by zero error encountered.

Die Verwendung von PRINT-Anweisungen im CATCH-Handler ist etwas, was gerne während der Entwicklung gemacht wird, für produktiven Anwendungscode jedoch nicht eher nicht gewünscht ist.
Wenn der Code im TRY Block gespeicherte Prozeduren oder Trigger aufruft, wird jeder Fehler, der hierbei auftritt, auch die Ausführung an den Catch Block weiterleiten. Genauer gesagt, wenn ein Fehler auftritt, wickelt der SQL Server den Stack ab, bis er einen Catch-Handler findet. Sollte es keinen geben, so wird die Fehlermeldung an den Client weitergegeben.

Es gibt jedoch auch einige Einschränkungen und Fehler, die nicht innerhalb eines TRY-CATCH Blocks abgefangen werden können.

CREATE PROCEDURE inner_sp AS
   BEGIN TRY
      PRINT 'Hier erfolgt ein Print'
      SELECT * FROM NoSuchTable
      PRINT 'Hier wird nichts ausgegeben'
   END TRY
   BEGIN CATCH
      PRINT 'Hier ebenfalls nicht'
   END CATCH
GO
EXEC inner_sp

Ausgabe:

Hier erfolgt ein Print
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4 [Batch Start Line 10]
Invalid object name 'NoSuchTable'.

Der Code im TRY-Block wird zwar ausgeführt, jedoch erfolgt im Fehlerfall keine Übergabe an den CATCH-Block. Dieses Verhalten tritt bei allen Kompilierungsfehlern wie fehlende Spalten, inkorrekte Aliase, etc. auf.

Diese Fehler können nicht im selben Scope abgefangen werden in dem sie auftreten, aber sie können in einer äußeren Klammer behandelt werden, wie das folgende Beispiel zeigt.

CREATE PROCEDURE outer_sp AS
   BEGIN TRY
      EXEC inner_sp
   END TRY
   BEGIN CATCH
      PRINT 'Fehlermeldung: ' + error_message()
   END CATCH
GO
EXEC outer_sp

Dieses Mal erhalten wir folgende Ausgabe:

Hier erfolgt ein Print
Fehlermeldung: Invalid object name 'NoSuchTable'.

Der aufgetretene Fehler wird also erfolgreich durch den äußeren CATCH-Block abgefangen.

SET XACT_ABORT ON

Ihre gespeicherte Prozeduren sollten immer mit folgenden Anweisungen beginnen:

SET XACT_ABORT, NOCOUNT ON

Damit werden zwei Sitzungsoptionen aktiviert, die aus veralteten Gründen standardmäßig ausgeschaltet sind. Die Erfahrung hat jedoch gezeigt, dass es am besten ist, sie immer zu aktivieren. Das Standardverhalten in SQL Server, wenn kein TRY-CATCH vorhanden ist, besteht darin, dass bei einigen Fehlern die Ausführung abgebrochen und alle offenen Transaktionen zurückgesetzt werden, während bei anderen Fehlern die Ausführung mit der nächsten Anweisung fortgesetzt wird.

Wenn sie XACT_ABORT ON aktivieren, haben fast alle Fehler den gleichen Effekt: jede offene Transaktion wird zurückgerollt und die Ausführung wird abgebrochen. Es gibt einige wenige Ausnahmen, von denen die bekannteste Anweisung RAISERROR ist.

Die Option XACT_ABORT ist wichtig für eine zuverlässigere Fehler- und Transaktionsbehandlung. Insbesondere mit den Standardverhalten des SQL Servers gibt es mehrere Situationen, in denen die Ausführung abgebrochen werden kann, ohne dass eine offene Transaktion zurückgesetzt wird, selbst wenn sie TRY-CATCH verwenden. Wir haben ein solches Beispiel im vorherigen Abschnitt gesehen, wo wir gelernt haben, dass TRY-CATCH Kompilierungsfehler im selben Scope nicht abfängt. Eine offene Transaktion, die im Falle eines Fehlers nicht zurückgesetzt wird, kann große Probleme verursachen, da die Anwendung weiterläuft, ohne dass ein Commit oder ein Rollback erfolgt.

Für eine gute Fehlerbehandlung in SQL Server werden sowohl TRY-CATCH Blöcke als auch SET XACT_ABORT ON benötigt. Die oben genannte Option NOCOUNT hat nicht direkt etwas mit der Fehlerbehandlung zu tun, empfiehlt sich jedoch dennoch in fast jedem Code. Die Wirkung von NOCOUNT besteht darin, dass Meldungen wie “1 Zeile(n) betroffen” unterdrückt werden, die Sie beispielsweise auf der Registerkarte “Meldung” im SQL Server Management Studio sehen können. Diese Zeilenzählungen können zwar nützlich sein, wenn Sie interaktiv in SSMS arbeiten, sie können aber die Leistung einer Anwendung aufgrund des erhöhten Netzwerkverkehrs beeinträchtigen, insbesondere wenn Schleifen in T-SQL verwendet werden oder das eigentliche Resultset recht klein ausfällt. Die zusätzlichen Rückmeldungen können auch schlecht geschriebene Clients verwirren, die sie für echte Ergebnismengen halten.

Mainzer Datenfabrik - T-SQL Error Handling

Die oben verwendete Syntax zur Aktivierung von XACT_ABORT und NOCOUNT ist etwas kürzer gehalten, Sie können stattdessen auch zwei separate Statements verwenden.

SET NOCOUNT ON
SET XACT_ABORT ON

Allgemeine Vorlage zur Fehlerbehandlung

Nachdem wir uns nun TRY-CATCH und XACT_ABORT ON angeschaut haben, wollen wir die Anweisungen zu einer allgemeinen Vorlage zusammenfassen, die wir in allen unseren gespeicherten Prozeduren verwenden können. Dazu starten wir mit einem simplen Beispiel.

Wir verwenden eine einfache Tabelle, in die wir im nächsten Schritt Daten einfügen wollen.

CREATE TABLE einetabelle (
    a int NOT NULL,
    b int NOT NULL,
    CONSTRAINT pk_einetabelle PRIMARY KEY(a, b))

Folgende Prozedur zeigt, wie Sie mit Fehlern und Transaktionen umgehen sollten.

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT einetabelle(a, b) VALUES (@a, @b)
      INSERT einetabelle(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      ;THROW
   END CATCH

Die erste Zeile der Prozedur aktiviert, wie wir bereits gelernt haben, die Optionen XACT_ABORT und NOCOUNT. Dies ist die einzige Zeile, die vor unserem BEGIN TRY stehen muss. Alles andere (Variablendeklarationen, Erstellung von temporären Tabellen, Tabellenvariablen, etc.) sollte erst danach kommen. Auch wenn Sie andere SET-Befehle benutzen sollten, können diese nach BEGIN TRY aufgelistet werden.

Der Kern der Prozedur steht dann zwischen BEGIN TRY und END TRY. In unserem Fall starten wir eine Transaktion, in der Zahlen in unsere Zieltabelle geschrieben werden. Sowohl in der Reihenfolge, wie der Anwender sie angegeben hat, als auch in umgekehrter Reihenfolge. Die beiden INSERT Statements befinden sich dabei zwischen BEGIN und COMMIT TRANSACTION. Manchmal gibt es auch einen Code zwischen COMMIT TRANSACTION und END TRY, obwohl hier in der Regel nur ein abschließendes SELECT zur Rückgabe von Daten oder zur Zuweisung von Werten an Ausgabeparameter steht. Wenn Ihre Prozedur keine Aktualisierungen durchführt oder nur eine einzige INSERT/UPDATE/DELETE/MERGE-Anweisung enthält, benötigen Sie normalerweise überhaupt keine explizite Transaktion.

Während der TRY Block von Prozedur zu Prozedur unterschiedlich aussieht, gilt dies nicht für den CATCH Block. Ihre CATCH Blöcke sollten mehr oder weniger per Copy/Paste eingefügt werden. Das heißt, sie entscheiden sich einmal für eine Vorgehensweise und verwenden diese dann in allen Prozeduren, ohne viel darüber nachdenken zu müssen ,wie der jeweilige CATCH Block innerhalb der Prozedur funktioniert und wie er sich von anderen Prozeduren unterscheiden könnte.

Der CATCH Block in unserem Beispiel führt zwei Aktionen aus:

Zurückrollen noch offener Transaktionen
Ausgabe der Fehlermeldung

Natürlich könnten Sie argumentieren, dass die Zeile

IF @@trancount > 0 ROLLBACK TRANSACTION

nicht benötigt wird, da sie keine explizite Transaktion in der Prozedur verwenden. Bedenken Sie aber folgendes: Vielleicht rufen Sie eine gespeicherte Prozedur auf, die eine Transaktion startet, die aber aufgrund der Einschränkung von TRY-CATCH nicht zurückgerollt werden kann. Oder aber Sie oder jemand Anderes fügt in zwei Jahren eine explizite Transaktion zu der Prozedur hinzu. Werden Sie dann noch daran denken, die Zeile für das Rollback einzufügen? Wir empfehlen lieber JETZT auf Nummer sicher gehen.

Mittels THROW wird die aufgetretene Fehlermeldung zurückgegeben. Beachten Sie dabei, dass die Anweisung vor THROW mit einem Semikolon enden sollte. Auch wenn es in der offiziellen Dokumentation keines Semikolons bedarf, gehen wir auch hier lieber auf Nummer sicher und beenden das vorangegangene Rollback mit einem Semikolon.

Die letzte Anweisung in unserer Prozedur ist END CATCH. Normalerweise sollte kein Code nach dem END CATCH (des äußerten TRY-CATCH Blocks, sofern diese verschachtelt sind) stehen.

Testen wir unsere Prozedur anhand eines Beispiels:

EXEC insert_data 9, NULL

Wir erhalten folgende Ausgabe:

Msg 515, Level 16, State 2, Procedure insert_data, Line 6 [Batch Start Line 19]
Cannot insert the value NULL into column 'b', table 'master.dbo.einetabelle';
column does not allow nulls. INSERT fails.

`THROW` vs `RAISERROR`

Bevor Microsoft mit SQL Server 2012 THROW zur Ausgabe von Fehlermeldungen eingeführt hat, wurde dieser Part meist von RAISERROR übernommen.

BEGIN CATCH
  IF @@trancount > 0 ROLLBACK TRANSACTION
  DECLARE @msg nvarchar(2048) = error_message()
  RAISERROR (@msg, 16, 1)
  RETURN 55555
END CATCH

Mit RAISERROR können Fehler entweder auf der Grundlage einer Fehlernummer oder Meldung ausgegeben werden und Sie können den Schweregrad und den Zustand des Fehlers definieren.
Wenn sie RAISERROR mit einer Fehlernummer aufrufen, muss diese in der Systemtabelle sys.messages vorhanden sein.

Warum sollte RAISERROR jedoch verwendet werden, wenn Microsoft doch THROW als neues cooles Feature eingeführt hat?

Das Problem besteht, wenn die “alte” Fehlerbehandlung mit der “neuen” Fehlerbehandlung gemischt wird. Viele Applikationen können bereits seit etlichen Jahren bestehen und auch wenn Sie vielleicht eine neuere SQL Server Version verwenden, kann der Code gespeicherter Prozeduren gut und gerne älter als zehn Jahre sein. Diese Prozeduren können durchaus über 10-15 Ebenen verschachtelt sein und es besteht die Möglichkeit, dass neue bzw. geänderte Prozeduren Teil einer Aufrufkette sind und nicht unbedingt die letzte Prozedur dieser Kette darstellen. Tritt dieser Fall ein, so kommt es zu Problemen mit der Ausgabe der Fehlermeldungen äußerer Prozeduren.

Hierzu erweitern wir unser bestehendes Beispiel um eine zusätzliche aufrufende Prozedur:

CREATE PROCEDURE call_insert @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      EXEC insert_data @a, @b
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

EXEC call_insert 8,8

Wir erhalten folgende Ausgabe:

Msg 50000, Level 16, State 1, Procedure call_insert, Line 10 [Batch Start Line 34]
Violation of PRIMARY KEY constraint 'pk_einetabelle'.
Cannot insert duplicate key in object 'dbo.einetabelle'
The duplicate key value is (8, 8).

Beachten Sie die erste Zeile der Fehlermeldung. Laut Ausgabe tritt der Fehler in der gespeicherten Prozedur “call_insert” auf. Wir wissen jedoch, dass diese Prozedur lediglich die Prozedur “insert_data” aufruft. Auch wenn der Fehler an sich eindeutig ist und es in unserem Fall ziemlich offensichtlich scheint, wo genau das Problem liegt, kann dieses Verhalten große Nachteile mit sich bringen. Im Falle von 10 verschachtelten Prozedur-Aufrufen und einer komplexeren Fehlermeldung kann es sehr lange dauern, herauszufinden, wo genau das Problem liegt.

Schreiben wir also unsere äußere Prozedur um, indem wir auch an dieser Stelle anstatt RAISERROR die Funktion THROW verwenden und schauen uns anschließend die Ausgabe an.

Msg 2627, Level 14, State 1, Procedure insert_data, Line 7 [Batch Start Line 32]
Violation of PRIMARY KEY constraint 'pk_einetabelle'.
Cannot insert duplicate key in object 'dbo.einetabelle'.
The duplicate key value is (8, 8).

Nun sehen wir in der ersten Zeile die eigentliche Prozedur, in der der Fehler aufgetreten ist.

Fazit

Wir haben nun also gelernt, wieso eine Fehlerbehandlung in T-SQL sinnvoll ist und welche Vorteile sie mit sich bringt. Uns ist eine allgemeine Vorgehensweise bekannt, die auf gut und gerne 90 - 95% unseres Codes in gespeicherten Prozeduren angewendet werden kann.
Natürlich lässt sich diese Vorlage noch erweitern, um beispielsweise Fehlermeldungen in Log-Tabellen zu speichern und weitere Maßnahmen zu ergreifen, um mit aufgetretenen Fehlern besser umgehen zu können.
Wir sollten darauf achten, welche eventuell bereits bestehenden Mechanismen zur Fehlerbehandlung in unserem Code existieren, bevor wir “neue” Features wie THROW in unseren Code mit einbauen.

Sie benötigen professionelle Unterstützung bei der Umsetzung Ihrer Projekte oder dem Review Ihres Codes? Unsere Expert:innen helfen Ihnen gerne weiter! Kontaktieren Sie uns gerne einfach über das Kontaktformular.

T-SQL Error Handling

Sinn und Zweck der Fehlerbehandlung

Essentielle Kommandos

`TRY-CATCH`

SET XACT_ABORT ON

Allgemeine Vorlage zur Fehlerbehandlung

`THROW` vs `RAISERROR`

Fazit

Auf dieser Seite

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.

T-SQL Error Handling

Sinn und Zweck der Fehlerbehandlung

Essentielle Kommandos

TRY-CATCH

SET XACT_ABORT ON

Allgemeine Vorlage zur Fehlerbehandlung

THROW vs RAISERROR

Fazit

Auf dieser Seite

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.

`TRY-CATCH`

`THROW` vs `RAISERROR`