Apply operator is of two types: Cross Apply and outer apply. Cross Apply is more like left outer join and Outer apply is more like outer join. Below we will see by an example as how it works and can be helpful in querying the table valued function. We will use AdventureWorks database for this example.
Apply operator can be leveraged to query table valued function and results a row once per every row of outer table.
Let's create the table valued function first:
Create FUNCTION getHighestQuantity
select distinct top(@num) p.ListPrice ,p.Name,color,OrderQty
from SalesLT.SalesOrderDetail od inner join
ON od.ProductID =p.ProductID
where p.ProductID like '%'+@prodID+'%'
order by OrderQty desc
This is a simple table valued function that will return details of product that sold highest quantity.
Query the table valued function using cross apply
from dbo.Products p cross apply dbo.getHighestQuantity(10,p.ProductID )gt
--In this we see that for every row of the table is an argument to the table valued function