Returning errors from SQL Server Stored Procs

[Khalizan]

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

[Khalizan]

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