Test insert fail in stored proc

[MaxDB - Mailing List]

Hello All,


Is there a way , within the stored proc, to test if an insert fails?

I have a stored procedure that includes below snip of code.  Basically the
proc test for an existing id based on some selection criteria.  If the ID
does exists the value of intExistingID is set to -10 and the returnval
is -1.  If the id does not exist intExistingID is set to a positive number
and then the else branch below is executed.  I am doing the insert and then
wish to return the currval when the insert is complete.  However Field2 does
not allow nulls.  If the insert executes with it being Null then the insert
fails. If this is done within the same session CURRVAL returns a number even
when the insert fails.  Is there a way , within the stored proc, to test if
the insert failed, that way I can set SET intReturnVal = -2; and know it
failed?

    IF intExistingID = -10 THEN
    BEGIN
        SET intReturnVal = -1;
    END
    ELSE
    BEGIN
        INSERT INTO DBM.THIS_TABLE(
           FEILD1,
           FEILD2,
           FEILD3
        )
        VALUES(
            :Value1,
            :Value2,
            :Value3
        );

         SET intReturnVal = THIS_TABLE.CURRVAL;

    END
    ;

Thanks for all your help,


-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]