You have an error in your code. Try this:

CREATE OR ALTER PROCEDURE COUNTRIES_LP
returns (
id bigint,
name varchar(30),
iso2 varchar(2),
iso3 varchar(3),
telcode smallint,
created_at date,
updated_at timestamp)
as
declare variable trans_name varchar(30);
begin
for select id, name, iso2, iso3, telcode, created_at, updated_at
from countries
into :id, :name, :iso2, :iso3, :telcode, :created_at, :updated_at
do
begin
select name from countries_l
where
:id = countries_l.fk_country_id and
countries_l.lang_code5 = rdb$get_context('USER_SESSION',
'LANG_CODE')
into :trans_name;
if (:trans_name is not Null) then
name = :trans_name;

suspend;
end
end;

The problem was in the begin..end clause after for select and suspend.
You've called suspend only once, so there is no surprise that SP
returned you only 1 record.

Milan


Dne 17.9.2010 13:49, Werner F. Bruhin napsal(a):

>
> My test table "countries" contains two rows and with this stored
> procedure I only get one row.
>
> CREATE OR ALTER PROCEDURE COUNTRIES_LP
> returns (
> id bigint,
> name varchar(30),
> iso2 varchar(2),
> iso3 varchar(3),
> telcode smallint,
> created_at date,
> updated_at timestamp)
> as
> declare variable trans_name varchar(30);
> begin
> for
> select id, name, iso2, iso3, telcode, created_at, updated_at from
> countries
> into :id, :name, :iso2, :iso3, :telcode, :created_at, :updated_at
> do
> begin
> select name from countries_l
> where :id = countries_l.fk_country_id and
> countries_l.lang_code5 = rdb$get_context('USER_SESSION',
> 'LANG_CODE')
> into :trans_name;
> end
> if (:trans_name is not Null) then
> begin
> name = :trans_name;
> end
> suspend;
> end;
>
> If I remove the select on "countries_l" I see the two rows. I thought
> between the "do" and the "suspend" I can have other selects etc to
> change what is being output, but I am obviously missing something.
>
> Can someone please put me on the right path.
>
> Thanks
> Werner
>

[Non-text portions of this message have been removed]