Hi Winston,

I'd rather recommend you to do things the opposite way:

create procedure usingInPredicate ( inList varchar(500))
returns (studentId integer)
as
declare variable someValue integer;
declare variable someID integer;
begin

for
select studentId FROM secondStoredProcedure( inList)
into :someID
do
begin
for
select a.some_field
from a inner join
b on a.fielda = b.fielda inner join
c on c.fieldb = b.fieldc
where
c.studentId = :someID
into :someValue
DO
begin
...

The reason being that earlier versions of Firebird used to evaluate
the subselect for every potential row of the main select. Newer
versions try to 'recode' the subselect to an EXISTS (which is faster)
if possible, but I've no idea whether it will do this when the
subselect refers to a stored procedure. Hence, at least in theory, you
risk your secondStoredProcedure to evaluate a lot more times than you
intended (if 'select * FROM secondStoredProcedure( inList )' and
'select * from A' return potentially 1000 rows each, then 'select *
from A where a.field in (select * FROM secondStoredProcedure( inList )
) may result in 1000000 intermediate results from
secondStoredProcedure).

The IN predicate is in general OK, but use it carefully when combining
it with subselects. Myself, I generally replace IN (<subselect>) with
EXISTS or JOIN and consider that better alternatives.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Winston" wrote:
> Thanks a lot.
>
> So, does this mean that I cannot (or it's not a good idea) to use
> the IN predicate at all in a stored procedure? The example you gave
> using 2 stored procedures is helpful :-) but it still boils down to
> not using the IN predicate.
>
> Or... can i do something like:
>
> create procedure usingInPredicate ( inList varchar(500))
> returns (studentId integer)
> as
> declare variable someValue integer;
> begin
>
> for
> select a.some_field
> from a inner join
> b on a.fielda = b.fielda inner join
> c on c.fieldb = b.fieldc
> where
> c.studentId IN (
> select studentId FROM secondStoredProcedure( inList )
> )
> into :someValue
> DO
> begin
> // process here....
>
> // return values we want
>
> studentId = :someProcessedValue
> suspend;
> end
>
> end!!
>
> where the secondStoredProcedure extracts the comma separated values
> of inList and returns(suspends) after each value is extracted and
> then 'beheads' the list there, too....