> I am writing a payroll program. I need to display on a check stub the
> current amount and the year to date amount for all of the deductions.
> I would like to do something like
>
> SELECT t.Description, d.CurrentAmount, sp.YTDAmount
> FROM Detail d
> INNER JOIN PayType t ON t.hKey = d.hPayType
> INNER JOIN sp_YTDAmt(d.hCheck, d.hPayType) sp ON 1=1
>
> but I am not sure about what to put in the ON clause of the inner join
> to the stored procedure. I reviewed the section in The Firebird Book
> on selectable stored procedures and I have searched the newsgroup but
> I am not finding anything that touches on this directly.
>
> Is it just that you cannot join to a stored procedure?

No it is possible to join to a stored procedure, you just need to make
sure the optimiser does not attempt to use the stored procedure before
it has used the tables required in its input parameters. This can be
achieved by changing it to a left join, and using a where clause to
force an inner join result.

SELECT t.Description, d.CurrentAmount, sp.YTDAmount
FROM Detail d
INNER JOIN PayType t ON t.hKey = d.hPayType
LEFT JOIN sp_YTDAmt(d.hCheck, d.hPayType) sp ON 1=1
WHERE sp.YTDAmount is not null

> It appears
> that I can rewrite this using a stored procedure to enclose a cursor
> that first gets the Description and CurrentAmount and then calls
> another stored procedure to get the YTDAmount. Is this the right
> approach?

Whether it is the right approach depends on what the stored procedure
is doing. If you are not careful you can easily make it very inefficient.

Adam