CODEWITHSUNDEEP
sql-serversql-server-2005user-defined-functions
Tim Schmelter
Tim Schmelter

Reputation: 460238

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 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

Upvotes: 1

Views: 565

Answers (1)

marc_s
marc_s

Reputation: 755148

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:

Upvotes: 5

Related Questions