数据库函数

数据库表

表值函数

create function [dbo].[GetDirectionIDList] (@id int,@BuyerOrgID int)
returns @t table(id int)
as
begin
 insert @t select DirectionID from dbo.gpo_direction where ParentID = @id and BuyerOrgID = @BuyerOrgID
 while @@rowcount > 0
 insert @t select a.DirectionID from gpo_direction as a inner join @t as b
 on a.ParentID = b.id and a.DirectionID not in(select id from @t)
 return
end
GO

递归（表值函数）

标量值函数

create FUNCTION [dbo].[GetItemPurchaseCount]
(
    @PurchaseID int
)
RETURNS int
AS
BEGIN
    Declare @Res int;
    set @Res = 0;
    select @Res = COUNT(1) from gpo_purchase_item where PurchaseID = @PurchaseID;
    RETURN @Res;

END

GO

标量值函数