At 20/02/2002 10:15 AM, you wrote:

>Hello:
>
>I am using Interbase 6 and Delphi 5. I have a number of stored
>procedured that I assumed were working correctly until I had an error
>the other day. In my stored procedure I lookup a record and get some
>parameters as:
>
>(A)
>SELECT FIELD1,FIELD2
>FROM TABLE1
>WHERE FIELD3=:XXXX
>INTO :FIELDX,:FIELDY
>
>I then do an update on another table based on the :FIELDX and :FIELDY
>as:
>
>(B)
>UPDATE TABLE2
>SET FIELDA =:FIELDX
>WHERE FIELD4 = :FIELDY
>
>Problem is if the SELECT (A) fails then UPDATE (B) fails but neither
>returns an error.
>
>I have looked into SQLCODE as the Language Reference shows but I get
>an unknown token error plus I'm not sure SQLCODE will return an error
>if SELECT (A) fails since it's not really an error. Is there a way I
>can determine if SELECT (A) returned a record and if UPDATE (B)
>actually updated a record?

Yes you can check by checking if the result of (A) is null.

i.e.:
if (FIELDX is not null) then
UPDATE statement

As for the UPDATE statement. I think this has been discussed for Firebird,
but unknown where it's applied and which version it's going to be
available. The work around would be to do a "SELECT COUNT(*) FROM TABLE2
WHERE FIELD4 = :FIELDY" to know how many records where updated.

Hope this helps.


Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.accramed.ca)