sql join of function

Question

I have a function that takes 2 parameters and return a table. Is it possible to use this function in a select and show the 3 columns that it returns? This is my query:

select a.PersonId, b.Passportnumber, dbo.fn_Passport(a.PersonId)
from Person a

thanks

Solution

For SQL Server 2005+, you can use the APPLY operator

select a.PersonId, b.Passportnumber, p.col1, p.col2, p.col3
from Person a
OUTER APPLY dbo.fn_Passport(a.PersonId) p

I have assumed the column names are col1, col2, col3 for illustration.

You use CROSS APPLY when the function must return 1 or more rows to retain the Person record. Use OUTER APPLY to keep the Person record even if the function results in no rows. Basically

CROSS APPLY similar to INNER JOIN
OUTER APPLY similar to OUTER JOIN

How to use APPLY