[Sybase] Error handling in stored procedures

[apa]

Op Sybase 12.5.1 heb ik een stored procedure die een andere stored procedure oproept. De bedoeling is dat de eerste mogelijke errors van de tweede opvangt.

Als voorbeeld geef ik hier de 2 stored procedures:

code:

CREATE PROCEDURE STP_Called
AS
BEGIN
  SELECT Value = CONVERT(int, 'tekst kan niet naar int geconverteerd worden')
END

code:

CREATE PROCEDURE STP_Caller
AS
DECLARE @status int
BEGIN
  EXECUTE @status = STP_Called
  IF (@@error<>0) OR (@status<>0)
  BEGIN
    PRINT 'Error occured and captured'
  END
END

Als ik STP_Caller nu oproep, dan zou ik verwachten om de tekst "Error occured and captured" te zien verschijnen (tussen de foutboodschappen door), maar dat gebeurt dus niet. Eens de fout opgetreden is in STP_Called, stopt de hele uitvoering en krijgt de error-handling code in STP_Caller niet eens de kans zijn werk te doen.

Wat zie ik over het hoofd? Iemand een idee?

Ter info: *soms* werkt die manier van werken wel. Bijvoorbeeld wanneer ik STP_Called als volgt schrijf:

code:

CREATE PROCEDURE STP_Called
AS
BEGIN
  RETURN 1
END

Heb diezelfde script nu ook getest op SQL Server 2000 en ook daar doet deze eigenaardigheid zich voor. Het blijkt dus niet aan een of andere Sybase-quirk te liggen (hoewel ik wel besef dat Sybase & SQL Server in een ver verleden nauw verwant waren).

Alvast bedankt!

[ Voor 17% gewijzigd door apa op 10-10-2006 15:27 . Reden: Aanvullingen ]

[apa]

Ik heb eindelijk wat informatie gevonden over dit probleem in dit document en ook deze.

Afhankelijk van de fout die optreedt zal een DB-server op een andere manier reageren. We kunnen de gevolgde werkwijze daarom indelen in een aantal categorieën:

• Statement termination: de statement die de fout veroorzaakt wordt afgebroken en een rollback van die statement vindt plaats. @@error wordt op een niet-nul waarde gezet om de fout aan te duiden.
  
  Dit doet zich voor bij run-time errors die niet behandeld worden door batch-abortion (zie verder).
• Scope-abortion: de huidige scope (stored procedure, user-defined functie of blok van losse SQL statements) wordt onderbroken. @@error wordt op een niet-nul waarde gezet, maar een stored procedure zal geen return-status weergeven. Indien de oproeper een variabele gebruikt om de return-status op te vangen, dan wordt de waarde van die variabele ook niet veranderd (je kan dus best die variabele op een niet-nul waarde initialiseren vóór de EXEC-statement in de oproeper.
  
  Dit soort fouten doet zich voornamelijk voor bij compilation errors. Het kan zich tijdens run-time voordoen wanneer je in een stored procedure ene temporary table gebruikt die gecreëerd wordt in een parent procedure/script (=de oproeper). Omdat de temporary table tijdens creatie van de stored procedure niet bestaat, zal de server op dat moment geen validatie kunnen uitvoeren; die validatie gebeurt op run-time. Als je dan naar een veld refereert dat niet in die temporary table bestaat, dan krijg je ene scope-abortion.
• Batch-abortion: de uitvoering van de hele batch (t.t.z. de blok van SQL statements dat van de client naar de server is doorgestuurd) wordt onderbroken. Een rollback van alle open transacties vindt plaats. @@error wordt op een niet-nul waarde gezet, maar aangezien de batch onderbroken werd kan je die waarde niet checken in de code van de oproeper. Dit soort fouten kan niet in T-SQL opgevangen worden.
  
  Batch-abortion komt voor bij volgende run-time fouten:
  • De meeste conversie-fouten (zoals van string naar numerieke waarde)
  • Een parameter te veel doorgegeven aan een stored procedure
  • Te veel geneste stored procedures (= stored procedures die andere stored procedures oproepen), functies of triggers
  • Deadlock victim
  • Een INSERT statement dat gebruik maakt van een EXEC-statement i.p.v. een SELECT waarbij het aantal kolommen dat door de EXEC weergegeven wordt verschillend is van het aantal kolommen voor de INSERT.
  • Out-of-space voor data-file of transactielog.
• Connection-termination: de client-connectie wordt onderbroken. In dit geval kan je zowieso niet checken op @@error.
  
  Dit soort fouten komt voor bij zeer ernstige fouten zoals stack overflows access violations, ...

Voor zover dit correct is, betekent het dat het onmogelijk is om alle fouten in T-SQL op te vangen. Ik kan erin komen dat een out-of-space fout of een deadlock niet op te vangen valt, maar waarom conversies (sommige, niet eens allen) dit probleem opleveren vind ik veel vreemder.
Mocht er hier iemand een manier weten om dit probleem te omzeilen: PLEASE let me know!