SQL Server passing table columns to a function's parameter

Question

I have an SQL function which returns the minimum and maximum selling price of an item. I'd like to make a query which gets other StockItem columns together with it's selling price

like so:

SELECT i.StockItemID ii, 
       i.Name, 
       i.Code, 
       pli.SellingPrice AS MinSellingPrice,
       pli.StandardSellingPrice AS MaxSellingPrice,
       i.WebDetailedDescription,
       i.WebAdditionalInfo,
       i.FeaturedItemDescription
FROM SC_StockItem AS i, 
     func_GetPrice(17, i.StockItemID, 5) pli

However this gives an error:

Msg 4104, Level 16, State 1, Line 12 The multi-part identifier "i.StockItemID" could not be bound.

any idea how I can do this ?

Thanks in advance

Answer 1

If that is a table valued function, then you can use OUTER APPLY:

select i.StockItemID ii, 
  i.Name, 
  i.Code, 
  pli.SellingPrice as MinSellingPrice, 
  pli.StandardSellingPrice as MaxSellingPrice,
  i.WebDetailedDescription, 
  i.WebAdditionalInfo, 
  i.FeaturedItemDescription
from SC_StockItem as i
OUTER APPLY func_GetPrice(17, i.StockItemID, 5) pli

From MSDN:

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.