|Subject||RE: [firebird-support] Is there a way to pass a result set as a parameter?|
>Hi, you could make it work with "execute statement" which allows you to execute dynamically created sql in strings.
> Well, you get the idea. The query that generates the list of
> ParentIDs, can be as simple or complicated as I would like. Is
> there a way to pass a result set as a parameter? There may be
> hundreds or thousands of ParentIDs that need to be passed.
> Any help would be appreciated.
If you use 2.1 I would recommend creating a GTT, something like this:
create global temporary table ids(id integer not null) on commit delte rows;
then you modify your procedure like this:
CREATE PROCEDURE UpdateByParentID (AValue integer)
UPDATE SomeTable SET SomeColumn = :AValue
WHERE ParentID in (select id from ids);
With setup like that, you can insert ids into a gtt, and withing same transaction execute a procedure. After you commit all rows will be deleted from gtt.
Check the docs for more info.