How to make left outer join a UD-function with any sql table?

Question

i have a table. i have a user defined function(UDF). My UDF returns me a table. i need left outer join my table and my UDF table.

Answer 1

Use SQL Server 2005's new APPLY clause. The APPLY clause let's you join a table to a table-valued-function. That let's you write a query like this:

SELECT  C.CustomerID, 
    O.SalesOrderID,
    O.TotalDue
FROM 
    AdventureWorks.Sales.Customer AS C
CROSS APPLY
    AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O
ORDER BY 
    CustomerID ASC, TotalDue DESC

The APPLY clause acts like a JOIN without the ON clause comes in two flavors: CROSS and OUTER. The OUTER APPLY clause returns all the rows on the left side (Customers) whether they return any rows in the table-valued-function or not. The columns that the table-valued-function returns are null if no rows are returned. The CROSS APPLY only returns rows from the left side (Customers) if the table-valued-function returns rows.

Source: Using CROSS APPLY in SQL Server 2005

EDIT: As Lieven mentioned, The CROSS APPLY is only necessary when you need to pass a value from your table to the UDF.

Answer 2

Edit

If you do not need to pass a parameter to your UDF, you can simply join the table with your UDF. If you need to pass parameters, use the CROSS APPLY as mentioned in the other answer.

SELECT *
FROM dbo.YourTable yt
     LEFT OUTER JOIN dbo.UDF_YourUDF() yd ON yd.YourTableID = yt.YourTableID