How to join a table with a table valued function?

Question

I have a user defined function:

create function ut_FooFunc(@fooID bigint, @anotherParam tinyint)
returns @tbl Table (Field1 int, Field2 varchar(100))
as
begin
  -- blah blah
end

Now I want to join this on another table, like so:

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f 
join ut_FooFunc(f.ID, 1) u -- doesn't work
where f.SomeCriterion = 1

In other words, for all Foo records where SomeCriterion is 1, I want to see the Foo ID and Desc, alongside the values of Field1 and Field2 that are returned from ut_FooFunc for an input of Foo.ID.

What's the syntax to do this?

Answer 1

You need CROSS APPLY not join.

The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table.

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f 
Cross apply ut_FooFunc(f.ID, 1) u
where f.SomeCriterion = ...

Answer 2

I know the thread is old, I was asked the same question, I did a test, the result as follows...

Records in FacCurrencyRate = 14264 while TestFunction returns 105 if executed independently.

    SELECT F.*, x.CurrencyKey, x.CurrencyName
    FROM ( 
           SELECT CurrencyKey, CurrencyName FROM dbo.TestFunction()
        ) x
    INNER JOIN [dbo].[FactCurrencyRate] F ON x.CurrencyKey = f.CurrencyKey;

The execution time is...

    (14264 rows affected)
    Table 'FactCurrencyRate'. Scan count 1, logical reads 75, physical reads 1, read-ahead reads 73, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCurrency'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 749 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

If I use the suggested answer as follows...

select F.*, x.CurrencyKey, x.CurrencyName from [dbo].[FactCurrencyRate] F
cross apply dbo.TestFunction() x

The execution time and result count is...

(1497720 rows affected)
Table 'FactCurrencyRate'. Scan count 1, logical reads 75, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 38110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCurrency'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2106 ms,  elapsed time = 43242 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

What I see here is that inner query brings out more correct set of results and execution time is much more efficient. Do correct me with better approach to accomplish the same!