Returning errors from SQL Server Stored Procs


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Returning errors from SQL Server Stored Procs

  1. #1
    Khalizan Guest

    Returning errors from SQL Server Stored Procs


    Hello guys,

    Can anyone please enlighten me on situations where running a data manipulation
    command that results in an error on SQL Server does NOT raise a corresponding
    error in a calling VB application?

    I have two tables with a parent-child relationship which is enforced through
    declarative referential integrity on all delete operations performed on the
    parent i.e. parent record may not be deleted if it has a child.

    As expected, executing "DELETE FROM parent WHERE parent_id = x" using the
    query optimiser produces an error and the statement is not executed. When
    I include the same statement as part of a stored procedure and then execute
    the stored procedure from the query optimiser, I also get an error at the
    statement as expected. Upon error the stored procedure rollbacks the entire
    transaction.

    Now, the perplexing situation is this: when I execute the stored procedure
    using a command object within a VB application, within a procedure that has
    a On Error Goto clause to catch errors, the error does not pop-up within
    the VB application.

    When the same VB procedure executes an insert or update operation upon the
    database using stored procedures, with data that violates some rule on the
    database, say leaving null values in non-nullable fields, those stored procedures
    produce errors and roll back the transactions, the same way the delete operation
    above is done. The only difference is that error conditions produced by insert
    and update operations within those stored procedures are reproduced as expected
    in the calling VB application.

    Is it a known bug that errors encountered within stored procedures which
    DELETEs data are NOT reproduced within the calling VB application?

    Here's a snippet from the stored procedure. Basically the delete operation
    is very simple and not much can go wrong with it:

    <snip>
    /* Trying to delete from parent table */
    DELETE FROM DonorId WHERE id = @id

    /* Unable to delete from parent table for whatever reason*/
    IF @@error <> 0
    BEGIN
    ROLLBACK TRAN NJSNewDonor_SP_delete
    raiserror(@@error, 16, 1)
    RETURN -1
    END

    /* All okay */
    IF @tc = 0
    COMMIT TRAN NJSNewDonor_SP_delete

    RETURN 0

    I'm using SQL Server 7.0.

    How the error that occurs in the above stored procedure does NOT get caught
    in the calling VB application wherelse a similar error in another stored
    procedure that does an INSERT or UPDATE
    DOES get caught in VB?

    Any hint would be appreciated. Thank you in advance.

    Khalizan

  2. #2
    Khalizan Guest

    Re: Returning errors from SQL Server Stored Procs


    Hi again,

    I have run further tests to isolate the problem and have narrowed it down
    to the fact that only errors from the first SQL statement in a stored procedure
    bubbles up into a VB On Error clause. I would like to confirm this finding
    or is it due to us not doing it the right way.

    Basically, here’s our findings:
    1. Generally, On Error Goto clause in VB catches SQL Server generated an
    error ONLY if the error occurred during the FIRST data manipulation statement
    within the transaction.
    2. In other words, if the error occurs during the SECOND or subsequent statements
    within the transaction, then the error will NOT be caught by VB’s On Error
    Goto clause.
    3. Regardless of whether the error was or was not caught by VB, all errors
    were correctly handled by SQL Server i.e. the entire multi-statement transaction
    was correctly rolled back as if none of the statements were executed.

    Some results obtained with test data:
    ( Here’s how to read the test results: As an example, in the first test,
    the multi-staged transaction consisted of two data manipulation statements
    contained within a BEGIN TRAN/END TRAN section. The first of these attempted
    to delete a record from the parent table which HAS a child record in the
    child table. The second attempts to delete another row from the parent table
    which does NOT have a child record in the child table. The result observed
    was that the error caused by the first of the two statements was properly
    caught by VB as expected. Also, SQL Server rolled back the transaction as
    expected).

    OK: This produces error in VB – Conflict With COLUMN REFERENCE
    BEGIN TRAN
    DELETE FROM parent table would result in orphaned child – ERROR EXPECTED
    DELETE FROM parent table would NOT result in orphaned child - No error
    expected
    END TRAN

    OK: This produces error in VB – Conflict With COLUMN REFERENCE
    BEGIN TRAN
    DELETE FROM parent table would result in orphaned child - ERROR EXPECTED
    INSERT INTO attempt to store NON-NULL value into non-nullable field - No
    error expected
    END TRAN

    OK: This produces error in VB – Cannot INSERT NULL value
    BEGIN TRAN
    INSERT INTO attempt to store NULL into non-nullable field - ERROR EXPECTED
    DELETE FROM parent table would NOT result in orphaned child - No error expected
    END TRAN

    Not OK: This produces NO error in VB although by right it should have
    BEGIN TRAN
    DELETE FROM parent table would NOT result in orphaned child - No error expected
    DELETE FROM parent table would result in orphaned child - ERROR EXPECTED
    END TRAN

    Not OK: This produces NO error in VB although by right it should have
    BEGIN TRAN
    DELETE FROM parent table would NOT result in orphaned child - No error expected

    INSERT INTO attempt to store NULL into non-nullable field - ERROR EXPECTED
    END TRAN

    Not OK: This produces NO error in VB although by right it should have
    BEGIN TRAN
    INSERT INTO attempt to store NON-NULL value into non-nullable field - No
    error expected
    DELETE FROM parent table would result in orphaned child - ERROR EXPECTED
    END TRAN

    Not OK: This produces NO error in VB although by right it should have
    BEGIN TRAN
    DELETE FROM parent table would NOT result in orphaned child - No error expected
    INSERT INTO attempt to store NULL into non-nullable field - ERROR EXPECTED
    END TRAN



    Full form of the stored procedures used in the above tests follow this pattern:

    /* Beginning of code snippet */

    /****** Object: Stored Procedure NJSNewPerson_SP_delete ******/
    /* deletes a record with given id from the NJSNewPerson table */
    CREATE PROCEDURE NJSNewPerson_SP_delete (@id id) AS

    DECLARE @tc int
    SELECT @tc = @@trancount

    IF @tc = 0
    BEGIN TRAN NJSNewPerson_SP_delete
    ELSE
    SAVE TRAN NJSNewPerson_SP_delete

    /* <Main Function> */
    DELETE FROM PersonId WHERE id = @id

    /* Unable to execute the main function */
    IF @@error <> 0
    BEGIN
    ROLLBACK TRAN NJSNewPerson_SP_delete
    raiserror(@@error, 16, 1)
    RETURN -1
    END

    DELETE FROM PersonId WHERE id = 2897 /* This record has child thus causes
    an error */

    /* Unable to execute the main function */
    IF @@error <> 0
    BEGIN
    ROLLBACK TRAN NJSNewPerson_SP_delete
    raiserror(@@error, 16, 1)
    RETURN -1
    END

    /* All okay */
    IF @tc = 0
    COMMIT TRAN NJSNewPerson_SP_delete

    RETURN 0

    /* End of code snippet */

    However, and this is interesting, putting the same procedures into a DELETE
    trigger ( so that the procedure would be carried out every time a deletion
    occurs) works regardless of the location where the error occurs within the
    trigger, for example:

    /* Begin second code snippet */

    CREATE TRIGGER PersonId_TRIG_FKdelete ON PersonId
    FOR DELETE
    AS

    IF @@rowcount = 0
    RETURN

    DECLARE @tc int
    SELECT @tc = @@trancount

    DELETE FROM PersonEmpAddr WHERE PersonFid IN (SELECT id FROM deleted)

    IF @@error <> 0
    BEGIN
    ROLLBACK TRAN
    raiserror(@@error, 16, 1)
    RETURN
    END

    DELETE FROM PersonAddresses WHERE PersonFid IN (SELECT id FROM deleted)

    IF @@error <> 0
    BEGIN
    ROLLBACK TRAN
    raiserror(@@error, 16, 1)
    RETURN
    END

    DELETE FROM Persons WHERE PersonFid IN (SELECT id FROM deleted)

    IF @@error <> 0
    BEGIN
    ROLLBACK TRAN
    raiserror(@@error, 16, 1)
    RETURN
    END

    IF @tc = 0
    COMMIT TRAN

    /* End of second code snippet */

    The VB code that uses the stored procedure is very simple and it follows
    the normal basic form:

    Public Sub ProcName
    On Error Goto ErrHandler

    (Code to prepare and execute command object)

    ExitRoutine:
    (Some cleanup code)
    Exit Sub

    ErrHandler:
    Msgbox “An error had occurred”
    Goto Exit Routine

    As it is, our workaround this problem is to repackage the transaction as
    a trigger instead of a stored procedure. This workaround works only if the
    transaction CAN by repackaged as a trigger. The workaround is unsatisfactory
    in the sense that it does not offer a solution for transactions that cannot
    be reformulated into the form of a trigger.

    To reiterate, I would like to confirm that this is indeed the behaviour of
    SQL Server in so far as error transmission to VB is concerned. If true than
    this holds a wide-ranging ramification for us as the design of multi-step
    stored procs will have to be revised at least in so far as error transmission
    to VB is concerned. The form of stored procs we currently use has at least
    been recommended by two authoritative texts on SQL Server.

    Again, thanks in advance, and thank you for all responses so far.

    Khalizan


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center