Thanks you just have to put a second suspend there and it works like charm! Seems to be fast enough too!

Now just another short question:
If I were to execute a user defined search in a stored procedure, what would be the best way to achieve this?

Like you have three (or more) possible search options.
Users can search for nameid, name, customfield.

The procedure should search for nameid if the input param is not null, and the same with the other fields.

How can I do this in a stored procedure?

Thanks again!

Zd

----- Original Message -----
From: Sasha Matijasic
To: firebird-support@yahoogroups.com
Sent: Tuesday, February 26, 2008 10:57 PM
Subject: RE: [firebird-support] Stored procedure to return multiple rows in a single string


> Sasha told me in our previous conversation that it would be possible to
> write a stored procedure that joins strings from multiple table rows
> into a list and returns it as a string.
>
> So
> row1
> row2
> row3
>
> would look like
> "row1, row2, row3" in a single string. The stored procedure would be
> called in a main select query.
>
> Unfortunately I'm not a master of stored procedures, and I can't seem
> to find enough info on Google on how to create such a listing
> procedure.
>
> Could someone point me in the right direction with an example?
>

Something like this:

create procedure list_names
returns (nameid integer, name varchar(20), members varchar(200) )
as
declare variable temp_member varchar(20);
begin
for
select nameid, name from nms
into :nameid, :name do
begin
members = '';
for
select membername from members
where nameid = :nameid
into :temp_member do
members = members || iif(members = '', '', ', ') || coalesce(:temp_member, '');

suspend;
end
end^

I haven't really tested it but from this you will be able to make it work.

Sasha





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