That's a funny result of a few errors!

I must admit that using a selectable stored procedure to do updates
confuses me and that I would try to avoid that if possible, but your
problem can be attributed to a small part of your stored procedure.

> for select e_code from table_a where id = :vA_ID into :vE_CODE do begin
> select c_id from table_c where a_id = :vA_ID and e_code = :vE_CODE into :vC_ID;
>
> if (vC_ID is null) then begin
> vC_ID = gen_id(gen_een,1);

The first e_code works as you intend it to, but once you get to the
second, the

select c_id from table_c where a_id = :vA_ID and e_code = :vE_CODE into
:vC_ID;

doesn't return any records. Does this mean that vC_ID is null? No, it
means that it doesn't get assigned any new value and that it stays at
the same number as you assigned to the previous record. The insert will
fail since the C_ID will be the same as you just inserted for the last
record. I don't know how Firebird handles such a situation, i.e. getting
a key violation on a record in a SELECT statement, but you've probably
just discovered how it actually does things.

For you, the solution should be simple, just set vC_ID to null before
each iteration of your loop.

Set

DISCLAIMER

This e-mail is so confidential that even reading this line is strictly
forbidden (as far as I know, no-one has ever done so). If you have
received this e-mail in error, please ascertain that you don't tell
anyone, least of all the sender.