sql server - INNER JOIN with Table-Valued Function not working

Question

I have a table valued function that returns a table. When I try to JOIN the table-valued function with another table I don't get any results, but when I copy the result of the function into an actual table and do the same join, then I get expected results.

The query looks something like this:

Select *
From myTable
INNER JOIN fn_function(@parm1, @param2)
ON ....

All up I have about 4 such queries and each one has slighly different function, but all the functions produce the same table but different data. For some of these queries the INNER JOIN works, but for others it does not.

Any suggesting why this happens?

Answer 1

With the table valued function you generally use Cross Apply.

Select *
From myTable m
CROSS APPLY fn_function(m.field1, m.field2)