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

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
Can T-SQL function return user-defined table type?

I have my own type: CREATE TYPE MyType AS TABLE ( foo INT ) and a function receiving it as a parameter: CREATE FUNCTION Test ( @in MyType READONLY ) RETURNS @return MyType AS ... can it return MyType or only TABLE repeating MyType's structure: …

Read More