begin try in SQL-Batch-Statements
22. Juni 2010Es kommt öfter vor, dass man im Batch mehrere SQL-Statements ausführen möchte, sei es in einer Stored Procedure oder im Execute SQL-Task von SSIS.
Meistens hat man folgende Anforderung:
Läuft ein Statement auf einen Fehler, soll ein Rollback der Statements gemacht werden. Außerdem soll natürlich dem aufrufenden System der Fehler gemeldet werden.
Lässt man einen Batch einfach so laufen, wird dieses Ziel nicht erreicht, da im Fehlerfall auch die Statements nach dem Statement, das den Fehler verursacht, ausgeführt werden.
Beispiel:
set nocount on
select 1
select 1/0
select 2
liefert:
———–
1———–
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.———–
2
In Versionen vor SQL Server 2005 musste man die Error-Variable auslesen, etwa so:
set nocount on
declare @fehler as int
set @fehler = 0
select 1
set @fehler = @fehler + @@error
select 1/0
set @fehler = @fehler + @@error
select 2
set @fehler = @fehler + @@error
if @fehler>0 begin
print ‘Ein Fehler ist aufgetreten’
end
was folgendes Ergebnis liefert:
———–
1———–
Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.———–
2Ein Fehler ist aufgetreten
Das Problem ist, man muss die Zeile “set @fehler = @fehler + @@error” nach jedem Statement schreiben, da sie nach jedem (!) Statement zurückgesetzt wird.
Leichter geht das in SQL 2005 mit begin try … end try - angelehnt an Konstrukte aus Programmiersprachen wie C#:
set nocount on
begin tryselect 1
select 1/0
select 2end try
begin catch
print ‘Ein Fehler aufgetreten’
end catch
Am Ergebnis
———–
1———–
Ein Fehler aufgetreten
sieht man, dass nach dem fehlerhaften Statement die Bearbeitung beendet wird.
Nun fehlen nur noch 2 Anforderungen:
Dass keine Datenmanipulation statt findet, erreicht man über eine Transaktion, die im catch-Block zurückgerollt (rollback) wird.
Dass der Aufruf dennoch den Fehler mitbekommt, erreicht man über einen raiserror.
Das fertige Skript sieht dann so aus:
set nocount on
begin tran
begin try/* hier die eigentlichen SQL-Statements schreiben */
select 1
select 1/0
select 2end try
begin catch
if @@trancount > 0 begin
rollback tran
end
declare @fehler_text nvarchar(4000)
set @fehler_text = ERROR_MESSAGE()
declare @fehler_severity int
set @fehler_severity = ERROR_SEVERITY()
declare @fehler_state int
set @fehler_state = ERROR_STATE()
RAISERROR (@fehler_text, — Message text.
@fehler_severity, — Severity.
@fehler_state — State.
)end catch
if @@trancount > 0 begin
commit tran
end
