CODEWITHSUNDEEP
sql
Itay.B
Itay.B

Reputation: 4111

sql join of function

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

Upvotes: 5

Views: 21732

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

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

Upvotes: 14

Hassan
Hassan

Reputation: 2633

if your talking about fn_Passport- it doesn't seem, that it is returning a table.

If it does - you can select only needed columns, or join via CROSS APPLY

Upvotes: 0

Related Questions