--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 12:20 PM 25/07/2005 +0000, you wrote:
> >Hello!
> >I created stored procedure that returns 0/1 depending if a user

has a

> >role or not. HasRole(UserID,Rolename):returns 0/1
> >
> >Now I'd like to run a select from all users which have a certain

role.

> >Why "select * from users, HasRole(users.id,'Admin')" is not

working?

>
> Because it can't. You can join a table to a selectable SP,

provided the SP

> has an output field that can be matched to a key in the table. It

is

> horribly inefficient, too. I regard it as one of those things you
> shouldn't try to do just because you can....
>
> Write an SP to process the users, calling your SP for each and

return your

> user list directly, along the lines of
>
> create procedure GetUserListForRole (Rolename varchar(31))
> returns (id BigInt, username varchar(31), whatever sometype, ....)
> as
> declare Result smallint;
> begin
> for select id, username, whatever, .... from users
> into :id, :username, :whatever, ...
> do
> begin
> execute procedure HasRole(:id, :Rolename)
> returning_values(:Result);
> if (Result = 1) then
> suspend;
> end
> end
>
> Then, your application does
>
> select * from GetUserListForRole('Admin')

Thank you! I got your point!
I was mainly working with Oracle where this kind of use is possible.
I was thinking about your solution beforehand. What bothers me is
that I have to declare all the return columns in a SP (There's no
rowtype variable?), then again I have to declare them in .NET app and
then tippically table changes...
But you are absolutely correct! Thanks again!