[ACCEPTED]-How to make left outer join a UD-function with any sql table? -sql-server-2005

Accepted answer

Score: 16

Use SQL Server 2005's new APPLY clause. The 15 APPLY clause let's you join a table to a 14 table-valued-function. That let's you write 13 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 12 like a JOIN without the ON clause comes 11 in two flavors: CROSS and OUTER. The OUTER 10 APPLY clause returns all the rows on the 9 left side (Customers) whether they return 8 any rows in the table-valued-function or 7 not. The columns that the table-valued-function 6 returns are null if no rows are returned. The 5 CROSS APPLY only returns rows from the left 4 side (Customers) if the table-valued-function 3 returns rows.

Source: Using CROSS APPLY in SQL Server 2005

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

Score: 3

Edit

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

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

Source: stackoverflow.com

[ACCEPTED]-How to make left outer join a UD-function with any sql table? -sql-server-2005

Stored Procedures to .sql files

SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE

IF/ELSE Stored Procedure

Conditional Operator in SQL Where Clause

SQL Server 2005 Get First and Last date for any Month in any Year

Set all the 0 values to NULL

Using bcp utility to export SQL queries to a text file

How to remove diagramming support objects from SQL Server?

Performance of COUNT SQL function

How to create sequential number column index on table with data