Error Handling inside a transaction

  • I'm trying to do the following:

    1) If there is an error inside a transaction, rollback and return -1

    2) If the error is outside the transaction, just return -1

    3) Is there anything in SQL similar to On Error goto errh:

    4) Ideally I would like to store the error details in log table.

    Could you please review the following code.

    --**********************************

    Use Northwind

    Create PROCEDURE u_errorTest AS

    DECLARE @intErrorint

    BEGIN

    SELECT * FROM CUSTOMERSzzzzzzzzzzzzz

    SET @intError = @@ERROR

    IF @intError <>0 GOTO Errh

    BEGIN TRANSACTION SelectCustomers

    --SELECT * FROM CUSTOMERS

    SELECT * FROM CUSTOMERSzzzzzzzzzzzzz

    SET @intError = @@ERROR

    IF @intError <>0 GOTO Errh

    COMMIT TRANSACTION SelectCustomers

    Return 1

    Errh:

    IF @@TRANCOUNT>0

    ROLLBACK TRANSACTION SelectCustomers

    INSERT INTO ERRORLOG(@@ERROR,GETDATE())

    Return -1

    END

  • I dont think you want to use -1, I think(someone confirm?) that SQL reserves -1 through -15 for its own use. Nothing quite like on error goto. Basically you have to check @@error after each line, then branch to a label and execute your logging/set your return variable/etc.

    Andy

  • You could have

    Create PROCEDURE u_errorTest AS

    DECLARE @intError int

    SELECT * FROM CUSTOMERSzzzzzzzzzzzzz

    SET @intError = @@ERROR

    IF @intError <>0 GOTO Errh

    BEGIN TRANSACTION

    SELECT * FROM CUSTOMERSzzzzzzzzzzzzz

    SET @intError = @@ERROR

    IF @intError <>0 GOTO TxErrh

    COMMIT TRANSACTION

    Return

    TxErrh:

    ROLLBACK TRANSACTION

    Errh:

    INSERT INTO ERRORLOG(@@ERROR,GETDATE())

    Return 1

    go

    I never like to name transactions as someone might think they can nest them and roll back bits - always a problem to control.

    If you want to use @@TRANCOUNT then you should save the value on entry to the sp and compare to that - just in case someone runs the SP inside a transaction.

    If you want to log errors like this it is probably better to call an sp to do it then you can change your log table in one place (you will only need to add single entries so not a problem with encapsulating the table) - I would add the spid too.

    0 is usually used as a successful return code - you could use return @intError.


    Cursors never.
    DTS - only when needed and never to control.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply