Subject Re: How to Join to a Selectable Stored Procedure
Author Adam
--- In, "Rick Debay" <rdebay@...> wrote:
> I didn't know you could pass parameters from other tables in to a stored
> procedure inside a query. This will be very useful.
> BTW, what error will you get if you don't use an outer join?

It is not the fact that it is an outer join that is important, but
rather the fact that you must be sure that the fields involved in the
input parameters are known before the stored procedure is run (for
obvious reasons).

Maybe one day the optimiser will be smart enough to ignore any plan
which would see the stored procedure used before the table(s) used in
the source, but for now it can't and a left join is a simple way of
ensuring the optimiser does this right. You may need to ensure that
you include something in the where clause to make sure it behaves like
an inner join.

I can't remember the exact error, it is pretty obvious though. If you
are desperate to see what it is, use a right join to force the stored
procedure to be used first.