SQL Server 2005 Table-Valued Functions and CROSS APPLY

I ran into a situation earlier today where I wanted to use a table-value function, which essentially is just a function that returns a table. But in my case I wanted to take that table and have it joined to another table to produce the desired results.

 

I had never really had to do something like that before and I quickly found out that you can't do a standard JOIN or subquery on a table-value function if you are also trying to pass it a value derived from the table you are joining on.

 

For example, this query will work:

 

select  d.RecordID,
          d.StudyID,
          d.TrackingID
from    MyTable d
join     dbo.MyFunction(@RecordID) m on d.RecordID = m.RecordID

 

However, this query will not work:

 

select  d.RecordID,
          d.StudyID,
          d.TrackingID
from    MyTable d
join     dbo.MyFunction(d.RecordID) m on d.RecordID = m.RecordID

 

I realize I could have accomplished what I wanted by eliminating the function all together and having a subquery to do this all, but the point wasn't to just get it working, it was to get it working and have this helper function (MyFunction) available to centralize some logic that is used in multiple places.

 

Luckily in SQL Server 2005 there is a CROSS APPLY clause that makes this trivial:

 

select            d.RecordID,
                    d.StudyID,
                    d.TrackingID
from             MyTable d
cross apply    dbo.MyFunction(d.RecordID) m

 

The APPLY clause acts like a JOIN without the ON clause and comes in two flavors: CROSS and OUTER. The OUTER APPLY clause returns all the rows on the left side 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 if the table-valued-function returns rows.

 

In my particular example the query returned the data that I needed and I was able to keep my logic centralized.

 

These articles were helpful as I was testing and learning about the CROSS APPLY clause:

 

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

http://www.sqlteam.com/article/returning-complex-data-from-user-defined-functions-with-cross-apply

 

As with any SQL queries, you should test performance accordingly and determine if this particular solution is the best approach or if something else works better. I experimented with a couple other solutions; however, none of them performed any better and they didn't allow me to re-use them easily, which is why I decided the CROSS APPLY was worth using.