Call TVF on every record of a table and concat results
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
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?
One Solution collect form web for “Call TVF on every record of a table and concat results”
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