Subject variable as parameter to IN operator
Author Daniel L. Miller
Can I use a procedure variable as a parameter in a stored procedure?

Example:
tablea: id INTEGER, other fields
tableb: id INTEGER, id2 INTEGER

CREATE PROCEDURE DO_IT (TARGET_ID INTEGER, MATCHED_IDS VARCHAR (200))
RETURNS (ID INTEGER)
AS
BEGIN
FOR SELECT id FROM tablea WHERE id=:target_id INTO :id DO SUSPEND;
matched_ids = matched_ids || ',' || target_id;
FOR SELECT id2 FROM tableb WHERE id=:target_id AND id2 NOT IN
(:matched_ids) INTO :id DO BEGIN
FOR SELECT id FROM do_it( :id, :matched_ids ) DO SUSPEND;
END
END !!

The logic I am attempting here is to have a table with a list of names,
and second table with a list of relations. Calling the function with a
base id should return a list of the id's that are related. However,
because it is possible to have multiple relations to the same id, I'm
trying to implement a way to keep a 'unique' selection of relation id's.

The only other way I see to do it is have a table set aside for scratch
purposes, and insert the records that are selected and then search
against them each time - then return the whole temp table, then delete
it. I'd like a way to avoid that, if possible.

--Daniel