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.