Subject Re: How to Join to a Selectable Stored Procedure
Author jrodenhi
Thank you. The addition of the WHERE statement cleared up a nagging
question that had not fully materialized - what if I don't want an
OUTER JOIN. I probably would have run into that in time.

I will wait until I have a problem with an inefficient procedure to
pursue the reasons for it.

Thank you very much for your advice.


> 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
> Adam