At 02:15 PM 20-02-02 +0000, 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 - put the logic into your SP to do the update only if there is a row in table1 that meets the criteria:

if (exists (SELECT FIELD1,FIELD2
FROM TABLE1
WHERE FIELD3=:XXXX )) then
begin
SELECT FIELD1,FIELD2
FROM TABLE1
WHERE FIELD3=:XXXX
INTO :FIELDX,:FIELDY;
/* if FIELD2 is nullable, then you have to check that too */
if FIELDY is not null then
begin
UPDATE TABLE2
SET FIELDA =:FIELDX
WHERE FIELD4 = :FIELDY;
end
end

If you really have to get confirmation that something actually got updated, then you'll have to add logic to count and return the figure. You can do that by defining a return value to send back to the app.

create procedure myproc ( input arguments)
returns (affectedrows integer)
as
declare...
begin
affectedrows = 0;
...
...
if FIELDY is not null then
begin
affectedrows = :affectedrows + (select count(*) from table2
where FIELD4 = :FIELDY);
if (affectedrows > 0) then
begin
UPDATE TABLE2
SET FIELDA =:FIELDX
WHERE FIELD4 = :FIELDY;
end
end
end

-- H

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________