Joining a table value function to a MSSQL query

Question

I have a table-value function that takes an ID number of a person and returns a few rows and columns. In another query, I am creating a SELECT that retrieves a lot of information about many people. How can I pass an id number from my main query to my function to sum a column and join it to my main query? I wish I didn't have a table value function since that would work easily, however, this function is used elsewhere and I'd like to reuse it. Perhaps this isn't even possible with a table-value function and I need to create a scalar one.

My main Query looks like this:

select id_num, name, balance  from listOfPeople 

And the table-value function looks like this:

calculatePersonalDiscount(id_number) 

I would like to do something like:

select id_num, name, balance  from listOfPeople left join  (   SELECT id_num, SUM(discount)   FROM calculatePersonalDiscount(listOfPeople.id_num) ) x ON x.id_num = listOfPeople.id_num 

But you can't pass listOfPeople.id_num into the function since it's not really the same scope.

Answer 1

In SQL Server 2005 you can use the CROSS APPLY syntax:

select id_num, name, balance, SUM(x.discount) from listOfPeople     cross apply dbo.calculatePersonalDiscount(listOfPeople.id_num) x 

Likewise there's an OUTER APPLY syntax for the equivalent of a LEFT OUTER join.