求助sql问题，如何在存储过程中调用函数

悠悠小仙仙

函数如下：

Alter function dbo.Pub_FN_FundsList
(
@d1 datetime,        --开始日期
@d2 datetime,        --结束日期
@sPrice numeric(18,2),        --每期金额
@Term int,        --收付周期(月)
@AheadDays int,        --提前收付天数
@Align bit        --对齐方式:0对齐,1后对齐       

)                          

returns @TB table
(
[id] [int] IDENTITY(1,1) NOT NULL,
AwokeDate datetime,  --提醒日期
BeginDate datetime,  --开始日期
EndDate datetime,  --结束日期
sPrice numeric(18,2),--支付租金数字
TrueDay int,--实际租住天数
DayPrice numeric(4,2),--日租金
MonthNumber int,--整月个数
ExtraDays int,        --不满整月的天数
MonthDays int      --本月天数       
)

as
Begin
declare @dtmp datetime
--------------------------------------------------------------------------------------
if(@Align=0)
Begin
set @dtmp=@d1;       
while @dtmp<=@d2
begin
          insert into @TB(BeginDate,EndDate,TrueDay,DayPrice,ExtraDays,MonthNumber,MonthDays)
values(@dtmp
,dateadd(d,-1,DATEADD(m,@Term,@dtmp))
--,DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)
,case when dateadd(d,-1,DATEADD(m,@Term,@dtmp))>@d2 then DATEDIFF(d,@dtmp,@d2+1)
when DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)=59  then null
when DATEDIFF(d,@d1,@dtmp+1)/30<>0 then null
else DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)end
,(@sPrice/@Term)*12/365
--,case when dateadd(d,-1,DATEADD(m,@Term,@dtmp))>@d2 then DATEDIFF(d,@dtmp,@d2+1)%31 else DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)%31 end
,case when dateadd(d,-1,DATEADD(m,@Term,@dtmp))>@d2 then DATEDIFF(d,@dtmp,@d2+1)%30
when DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)=29 then 0
when DATEDIFF(d,@d1,@dtmp+1)/30<>0 then null
else 0  end
,case when dateadd(d,-1,DATEADD(m,@Term,@dtmp))>@d2 then DATEDIFF(d,@dtmp,@d2+1)/30
when DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)=58 then 2
when DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1) =59 then 2
when DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)=89 then 3
when DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)=28 or DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)=29 then 1
else DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)/30 end
,DateDiff(d,DATEADD(MONTH,DATEDIFF(MONTH,0,@dtmp),0),DATEADD(MONTH,1+DATEDIFF(MONTH,0,@dtmp),0)))
    set @dtmp=dateadd(m,@Term,@dtmp)       
        end       
End
Else
begin
set @dtmp=@d2;
while @dtmp>=@d1
begin
          insert into @TB(BeginDate,EndDate,TrueDay,DayPrice,ExtraDays,MonthNumber,MonthDays)
values(dateadd(d,1,DATEADD(m,-1*@Term,@dtmp))
,@dtmp
--,DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)
,case when dateadd(d,1,DATEADD(m,-1*@Term,@dtmp))<@d1 then DATEDIFF(d,@d1,@dtmp+1)
when DATEDIFF(d,@d1,@dtmp+1)/30<>0 then null
else DATEDIFF(dd,dateadd(d,1,DATEADD(m,-1*@Term,@dtmp)),@dtmp+1)end
,case when DATEDIFF(d,@d1,@dtmp+1)/30<>0 then null else (@sPrice/@Term)*12/365 end
,case when dateadd(d,1,DATEADD(m,-1*@Term,@dtmp))<@d1 and DATEDIFF(d,dateadd(d,1,DATEADD(m,-1*@Term,@dtmp)),@dtmp+1)=29 then 0
--case when dateadd(d,1,DATEADD(m,-1*@Term,@dtmp))<@d1 then DATEDIFF(d,@d1,@dtmp+1)%30
when dateadd(d,1,DATEADD(m,-1*@Term,@dtmp))<@d1 then DATEDIFF(d,@d1,@dtmp+1)%30
when DATEDIFF(d,dateadd(d,1,DATEADD(m,-1*@Term,@dtmp)),@dtmp+1)=29 then 0
when DATEDIFF(d,dateadd(d,1,DATEADD(m,-1*@Term,@dtmp)),@dtmp+1)=59 then 0
when DATEDIFF(d,@d1,@dtmp+1)/30<>0 then null
else 0 end
   ,case when dateadd(d,1,DATEADD(m,-1*@Term,@dtmp))<@d1  then DATEDIFF(d,@d1,@dtmp+1)/30
when dateadd(d,1,DATEADD(m,-1*@Term,@dtmp))<@d1 or DATEDIFF(d,dateadd(d,1,DATEADD(m,-1*@Term,@dtmp)),@dtmp+1)=59 then 2
when DATEDIFF(d,dateadd(d,1,DATEADD(m,-1*@Term,@dtmp)),@dtmp+1)=59 then 2
when DATEDIFF(d,dateadd(d,1,DATEADD(m,-1*@Term,@dtmp)),@dtmp+1)=29 then 2
else DATEDIFF(d,dateadd(d,1,DATEADD(m,-1*@Term,@dtmp)),@dtmp+1)/30 end
,DateDiff(d,DATEADD(MONTH,DATEDIFF(MONTH,0,@dtmp),0),DATEADD(MONTH,1+DATEDIFF(MONTH,0,@dtmp),0)))
set @dtmp=dateadd(m,-1*@Term,@dtmp)
         
end
End
--------------------------------------------------------------------------------------
update @TB
set AwokeDate=(case when dateadd(d,-1*@AheadDays,BeginDate)<@d1 then @d1 else dateadd(d,-1*@AheadDays,BeginDate) end)
,BeginDate=(case when BeginDate<@d1 then @d1 else BeginDate end)
,EndDate=(case when EndDate>@d2 then @d2 else EndDate end)
    --,TrueDay=(case when EndDate>@d2 then DATEDIFF(D,BeginDate,@d2+1) else DATEDIFF(d,@dtmp,dateadd(d,-1,dateadd(m,@Term,@dtmp))+1)end)
   -- MonthNumber=TrueDay/30
   ,sPrice=(case when MonthNumber = 0 and ExtraDays=15 then (@sPrice/@Term)/2
            when MonthNumber = 0 then TrueDay*DayPrice
            when MonthNumber<>0 and ExtraDays<>0 then DayPrice*TrueDay
            else(@sPrice/@Term)*MonthNumber end)
   --TrueDay=(case when sPrice = @sPrice then null end)
  -- ,MonthNumber=TrueDay/30
  -- ,ExtraDays=TrueDay%30

--------------------------------------------------------------------------------------
return;       
End
go
select * from Pub_FN_FundsList('2017-5-22','2018-5-21',3000,3,5,0) order by AwokeDate
select * from Pub_FN_FundsList('2017-1-15','2017-12-31',3000,1,5,1) order by AwokeDate



我想在一个名为[dbo].[OA_LeaseContract_Finance_Proce_Init]这个的存储过程中直接调用这个函数，请问语法应该怎么写？

草帽路飞UU

存储过程：
SQL code

1. CREATE PROC dbo.OA_LeaseContract_Finance_Proce_Init
   
2.     @date1 DATE ,
   
3.     @date2 DATE
   
4. AS
   
5.     BEGIN
   
6.         SELECT  *
   
7.         FROM    Pub_FN_FundsList(@date1, @date2, 3000, 3, 5, 0)
   
8.         ORDER BY AwokeDate;
   
9.     END;
   
10. GO

复制代码

调用：

1. EXEC dbo.OA_LeaseContract_Finance_Proce_Init @date1 = '2017-06-13', -- date
   
2.     @date2 = '2017-06-13'; -- date

复制代码

乐哈哈yoyo

1. if object_id('tempdb..#Tmp_Tbl') is not null
   
2.                             drop table #Tmp_Tbl
   
3.                              
   
4.     CREATE TABLE #Tmp_Tbl (
   
5.                 [id] [int]  NOT NULL,
   
6.                 AwokeDate datetime,  --提醒日期
   
7.                 BeginDate datetime,  --开始日期
   
8.                 EndDate datetime,  --结束日期
   
9.                 sPrice numeric(18,2),--支付租金数字
   
10.                 TrueDay int,--实际租住天数
    
11.                 DayPrice numeric(4,2),--日租金
    
12.                 MonthNumber int,--整月个数
    
13.                 ExtraDays int,    --不满整月的天数
    
14.                 MonthDays int )     --本月天数   
    
15. 
    
16. 
    
17. INSERT INTO  #Tmp_Tbl  Select * From dbo. dbo.Pub_FN_FundsList(@d1,@d2,@sPrice,@Term,@AheadDays,@Align)
    

复制代码

八戒你干嘛

直接在过程中：
Select * From dbo. dbo.Pub_FN_FundsList('2017-06-17','2017-07-18',3000, 3, 5, 0)