Sql connection function

Question

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

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

thanks

Answer 1

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

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

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

You use CROSS APPLY when a function must return 1 or more rows in order to save a Person record. Use OUTER APPLY to keep a Person entry, even if the function has no rows. Mostly

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

How to use APPLY

Answer 2

If you are talking about fn_Passport, it does not seem to return a table.

If so, you can select only the columns you need or join CROSS APPLY