Hi Sven,
in case you're still interested, your procedure works now; I've
changed it a bit:

CREATE PROCEDURE GET_NEXT_20 (
asterm_in varchar(240),
id_in bigint)
returns (
id_out bigint,
pos integer)
as
declare variable id_temp bigint;
declare variable asterm_temp varchar(240) character set unicode_fss;
begin
pos = 0;
id_temp = :id_in;
asterm_temp = :asterm_in;
while (1 = 1) do
begin
select min(asterm) from dicentries where ((asterm = :asterm_temp
and id >= :id_temp)) or (asterm > :asterm_temp) into :asterm_temp;
if (asterm_temp is null) then
exit;
for select id from dicentries where asterm = :asterm_temp and id

>= :id_temp order by id ascending into :id_out do

begin
suspend;
pos = pos + 1;
if (:pos = 20) then
exit;
end
id_temp = :id_out + 1;
end
end

Also, see my other post from an hour ago for something very strange
with firebird 1.5:
http://tech.groups.yahoo.com/group/firebird-support/message/80244

Regards,

Martin

--- In firebird-support@yahoogroups.com, "martinknappe" <martin@...>
wrote:

>
> --- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
> <svein.erling.tysvaer@> wrote:
> >
> > Hi again!
> >
> > Your procedure is similar to what I suggested, but probably a bit
> > slower. At first glance, I do not see how it could return NULL, but I
> > haven't looked closely since this may be gone when you clean up your
> > procedure and if not, then it could possibly be easily

circumvented by

> > adding WHERE IDOUT IS NOT NULL to your calling query.
>
> well, that would prevent entries w/o an idout value from appearing in
> the result set, but it's not the solution to the problem; it's just a
> cover-up which effectively hides data away from the user when he is
> really requesting it..as a matter of fact, for a simple test, i
> changed my procedure in such a way that it also returned the proper
> value for asterm of the result set entries..what i did then was copy
> asterm of those entries that had null in their id field and queried
> the following:
>
> select id from dicentries where asterm = copiedValue
>
> and it did give me an id so this shows that the id really exists..i
> don't know what's going on here..im nearly so far as to claim this is
> a bug..
>
> > You seem to prefix variables with a colon everywhere.
>
> afaik that's absolutely necessary
>
> > Moreover, SET TERM is part of the
> > stored procedure language, so I would try to avoid having TERM as
> > the name of a parameter.
>
>
> ok, i tried with a different identifier; didn't help either
>
> > You combine SELECT FIRST with counting manually,
> > and that is just wasting time and resources.
>
> i know, i tried "select min(id)..." before but then tried with "first"
> just to see if that was the problem..it really kept me busy for a good
> while yesterday :-(
>
> > And finally, is ASTERM at
> > all indexed
>
> yes it is; in the first post of this thread theres a link to the sql
> script for creating the database where you can look everything up
>
> i've nearly given up..what ive done now is some kind of a
> workaround...whereas before the select procedure my client user would
> invoke did something like:
>
> select first 20 id from dicentries
> where (asterm = :asterm and id >= :id) or (asterm > :asterm)
> order by asterm ascending, id ascending
>
> i now changed the procedure so that it be more selective:
>
> select first 20 id from dicentries
> where ((asterm = :asterm and id >= :id) or (asterm > :asterm)) and
> (asterm <= :upperbound)
> order by asterm ascending, id ascending
>
> so now it's in the user's hand to make response time shorter by simply
> "cutting off" the end of the table, which he wouldn't have seen in his
> result set anyway...not the nices of solutions but better than keep
> the user waiting for 7 seconds on every page scroll...
>
> thanx anyway,
>
> martin
>