--- In firebird-support@yahoogroups.com, "Henk van der Meer"
<hvdmeer@c...> wrote:

> For example I am trying to write a stored procedure that takes two

integer

> parameters A and B. If A isn't NULL it returns A else it returns B.

I use

> the integers as id's.
>
> Suppose the stored procedure is called P_A_OR_B.
>
> I am able to use it like this: select * from P_A_OR_B(1,1). But not like
> this:
>
> Select * from table_x x
> Join table_y y on y.id = P_A_OR_B(x.id1,x.id2)
>
> The most frustrating thing is the error message: function unknown.
>
> I have lots of questions but will start with 2.
>
> 1) If the stored procedure was written properly would the second

usage (the

> join) be possible?

Exactly this - no. My understanding is - when you join table with
SP, FB can't built access bitmap using indices but should fetch both
streams and then join them. So, columns of the table can't be used as
input parameters for SP - their concrete values for each row simply
are not defined at the moment of SP call. On the other hand, output
parameters of SP (treated as temporary table's columns) can be used as
join conditions. So, you can do

select * from SP(:Param) P join Table T on P.id = T.id

the same are

select * from SP(:Param) P, Table T where P.id = T.id

select * from Table T, SP(:Param) P where P.id = T.id

all 3 statements are equivalent, last 2 are implicit (SQL89 standard)
syntax for INNER JOIN (SQL92) and optimizer feel itself free to select
which object will be basis to merge streams, i.e. "lead" join. And
will always make SP this object to get values to search corresponded
columns values in the Table, using appropriate index if it exists. You
can check it looking on used plans, in all cases SP will be mentioned
first. The only way which allows you to force Table to "lead" join is
LEFT JOIN with additional trick:

select * from Table T left join SP(T.id) on 0=0;

this "0=0" is used because syntax analyzer will not allow join without
condition. In this case optimizer will fetch records from Table and
use values of the columns to call SP for each row individually.

Best regards,
Alexander.