Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

INNER JOIN with Table-Valued Function not working

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?

like image 236
user2343837 Avatar asked May 01 '14 05:05

user2343837


People also ask

How do you join a table with a table valued function in SQL?

SQL Server does have a solution for this called CROSS APPLY. If you use CROSS APPLY for INNER JOINS and OUTER APPLY for LEFT OUTER JOINS, then you have the ability to create a join between two table valued expressions, which in my case is a TABLE VARIABLE and the results of a TABLE VALUED FUNCTION.

Can we use function with inner join in SQL?

INNER JOIN TABLE2 SQL Inner Join clause is the same as Join clause and works the same way if we don't specify the type (INNER) while using the Join clause. In short, Inner Join is the default keyword for Join and both can be used interchangeably.

Can we join a function and a table?

SQL Server supports table valued functions, what are functions that return data in the form of tables. JOIN operations in SQL Server are used to join two or more tables. However, JOIN operations cannot be used to join a table with the output of a table valued function. APPLY operators are used for this purpose.

Can you use CTE in table valued function?

You can only use a CTE within the context of DML language (SELECT, INSERT, UPDATE, DELETE, MERGE).


1 Answers

With the table valued function you generally use Cross Apply.

Select * From myTable m CROSS APPLY fn_function(m.field1, m.field2) 
like image 174
Anup Agrawal Avatar answered Sep 24 '22 05:09

Anup Agrawal