Call TVF on every record of a table and concat results

Question

I thought that must be obvious but I can't figure it out.

Say there is a table tblData with a column ID and a table-valued-function (_tvf) that takes an ID as parameter. I need the results for all ID's in tblData.

But:

SELECT * FROM tblData data 
INNER JOIN dbo._tvf(data.ID) AS tvfData
   ON data.ID = tvfData.ID

gives me an error: The multi-part identifier "data.ID" could not be bound

What is the correct way to pass all ID's to this TVF and concat the results?

Thanks

Answer 1

I think you might need to use CROSS APPLY instead of an inner join here:

SELECT * 
FROM dbo.tblData data 
CROSS APPLY dbo._tvf(data.ID) AS tvfData

This will call the TVF function for each data.ID of the base table and join the results to the base table's columns.

See ressources here:

• Using CROSS APPLY in SQL Server
• Understanding APPLY clause in SQL Server
• Using T-SQL CROSS APPLY and OUTER APPLY