I have a table function that works fine except when used as a JOIN.

If I have a function that I am passing a parameter from a value I had gotten
from my query:

SELECT
t1.nID
FROM table1 t1
JOIN dbo.udf_somefunction(t1.nID)

I get an error:

The multi-part identifier "t1.nID" could not be bound.

This works fine if I change the select as:

SELECT
t1.nID
FROM table1 t1
JOIN dbo.udf_somefunction(10)

How can I get this to work??

Thanks,

Tom

Hi Tom,

If you need to use values from one table in the joined table
expression, you need to use APPLY rather than JOIN. So you use:

SELECT t1.nID
FROM table1 AS t1
CROSS APPLY dbo.udf_somefunction(t1.nID) AS f;

Or, if you want to preserve rows that produce an empty resultset in
the applied table expression:

SELECT t1.nID
FROM table1 AS t1
OUTER APPLY dbo.udf_somefunction(t1.nID) AS f;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis