Using a User Defined Function in a View

Question

I wrote the code for a View which needs to call a user defined function which returns a table to join with it. The problem here is passing the parameter that this functions needs straight out of my view.

Below is the code of my view:

select
    GG.Gid,
    GG.StockType StockType,
    COALESCE(STC.Contract, 0) ContractId,
    COALESCE(C.[$Refex], null) ContractRefex,
    ST.[$Refex] StockTypeRefex
from 
(
    select
        G.Gid,
        coalesce(max(G.GEStockType), max(G.EAStockType)) StockType--,
        --case when coalesce(G.GEStockType, G.EAStockType) is null then null else coalesce(G.GEStartDate, G.EAStartDate) end StartDate
    from
    (
        select
            G.Gid, SI.StockType EAStockType, SI.[Date] EAStartDate, null GEStockType, null GEStartDate
        from Goods G
        inner join SiteIn SI on G.SiteIn=SI.[$Id]

        union

        select G.Gid, null EAStockType, null EAStartDate, GE.StockType, GE.EventOn
        from 
        (
            Select
                GE.Gid,  max(GE.EventOn) GEStartDate
            from GoodsEvent GE
            where GE.IsDeleted=0 and GE.[Type]='ST' and GE.EventOn < GETDATE()
            group by Gid 
        ) G
        inner join GoodsEvent GE on GE.Gid=G.Gid
            and G.GEStartDate=GE.EventOn
            and GE.[Type]='ST'
    ) G
    group by G.Gid
) GG
left outer join StockType ST on ST.[$Id]=GG.StockType
inner join (SELECT * FROM [dbo].StockTypeContractGetClosestStartDate(ST.[$Id]))
 STC on  GG.StockType = STC.[Parent]
 inner join Contract C On STC.Contract = C.[$Id]

And this is the code of my function:

CREATE FUNCTION StockTypeContractGetClosestStartDate
(
    @ParentId int
)
RETURNS  @StockTypeContract TABLE 
(
    [StartDate] [DateTime] null,
    [Parent] [int] not null,
    [Contract] [int] null
)
AS
BEGIN

 INSERT @StockTypeContract 
    SELECT TOP 1 STC.StartDate , STC.[$ParentId] , STC.Contract
     from StockTypeContract STC
        where STC.[$ParentId] = @ParentId AND STC.StartDate <= GETDATE() 
        order by STC.StartDate desc

    RETURN
END

It gives me an error when trying to pass ST.[$Id] to my function, the error is "The multi-part identifier ST.$Id could not be bound".

Is there any work-around for this?

Answer 1

You actually needs CROSS or OUTER APPLY. And from SO too

....
left outer join StockType ST on ST.[$Id]=GG.StockType
CROSS APPLY
[dbo].StockTypeContractGetClosestStartDate(ST.[$Id])
...

(I've simplified parenthesis here BTW, probably wrongly)

Your problem is "get a resultset from StockTypeContractGetClosestStartDate per ST.[$Id]

Answer 2

If I am correct your only inserting one record in the return table of your function, if that is the case then you can rebuild the function as a scalar function, this returns only one value and should solve the multi-part problem.

At the moment your trying to join with a possible "multi valued id".

see http://technet.microsoft.com/en-us/library/ms186755.aspx for the scalar function