SQL Server passing table columns to a function's parameter
11,409
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
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.
Author by
Jonny
Updated on June 16, 2022Comments
-
Jonny about 2 years
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