在视图中使用用户定义的函数

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.

我编写了一个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".

尝试将ST。[$ Id]传递给我的函数时，它给出了一个错误，错误是“多部分标识符ST。$ Id无法绑定”。

Is there any work-around for this?

这有什么解决方法吗？

2 个解决方案

#1

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

你实际上需要CROSS或OUTER APPLY。从SO也来

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

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

（我在这里简化括号BTW，可能是错误的）

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

你的问题是“从ST获得StockTypeContractGetClosestStartDate的结果集。[$ Id]

#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".

目前你试图加入一个可能的“多值id”。

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

有关标量函数，请参见http://technet.microsoft.com/en-us/library/ms186755.aspx

#1