Menu
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?
236
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.
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.
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.
You can only use a CTE within the context of DML language (SELECT, INSERT, UPDATE, DELETE, MERGE).
With the table valued function you generally use Cross Apply.
Select * From myTable m CROSS APPLY fn_function(m.field1, m.field2) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
