INNER JOIN with Table-Valued Function not working

sql-server function tsql sql-function
user2343837 picture user2343837 · May 1, 2014 · Viewed 55.1k times · Source

I have a table valued function that returns a table. When I try to JOIN the table-valued function with another table I don't get any results, but when I copy the result of the function into an actual table and do the same join, then I get expected results.

The query looks something like this:

Select *
From myTable
INNER JOIN fn_function(@parm1, @param2)
ON ....

All up I have about 4 such queries and each one has slighly different function, but all the functions produce the same table but different data. For some of these queries the INNER JOIN works, but for others it does not.

Any suggesting why this happens?

Answer

Anup Agrawal picture Anup Agrawal · May 1, 2014

With the table valued function you generally use Cross Apply. 

Select *
From myTable m
CROSS APPLY fn_function(m.field1, m.field2)

Related questions

Are execution plan for functions cached in SQL server?

Can any body help me in understanding if the execution plan for functions cached in SQL server? Any online resource for this?

Read More
Difference between scalar, table-valued, and aggregate functions in SQL server?

What is the difference between scalar-valued, table-valued, and aggregate functions in SQL server? And does calling them from a query need a different method, or do we call them in the same way?

Read More
Is it possible to have temp tables in a function?

Apparently, I can't use them. I'm getting an error message like: Invalid use of a side-effecting operator 'SELECT' within a function If I want to do something like this: select bleh into #temp from Blah ... inside a function.

Read More