sqlserver在函数中使用临时表

-- 根据大订单号查询已付供应商金额(已付-已退)
ALTER FUNCTION [dbo].[F__SupplierPayOffAmountBOrder]
(
 -- Add the parameters for the function here
  @BOrderId int 
)
RETURNS decimal
AS
BEGIN
 declare @payOffAmount decimal
 set @payOffAmount=0
 
 declare @SorderIdTemp table(ID int) --定义临时表
 
 insert INTO @SorderIdTemp(ID)
 select ID 
 from TongYe..Borders_Orders_s
 where ParentID=@BOrderId and IsValid=1 and OrderStatus<>17
 
 while exists (select ID from @SorderIdTemp)
 BEGIN
  DECLARE @sid int
  select TOP 1 @sid=ID from @SorderIdTemp
   
  set @payOffAmount = @payOffAmount + ISNULL(dbo.F__SupplierPayOffAmount(@sid),0)
  
  delete from @SorderIdTemp where ID=@sid
  
 END
 
 return Isnull( @payOffAmount,0)
END

sqlserver在函数中使用临时表

标签：