求助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
(
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)=59then 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 0end
,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))<@d1then 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
我想在一个名为.这个的存储过程中直接调用这个函数，请问语法应该怎么写？
存储过程：
SQL code
CREATE PROC dbo.OA_LeaseContract_Finance_Proce_Init
@date1 DATE ,
@date2 DATE
AS
BEGIN
SELECT*
FROM Pub_FN_FundsList(@date1, @date2, 3000, 3, 5, 0)
ORDER BY AwokeDate;
END;
GO
调用：
EXEC dbo.OA_LeaseContract_Finance_Proce_Init @date1 = '2017-06-13', -- date
@date2 = '2017-06-13'; -- date if object_id('tempdb..#Tmp_Tbl') is not null
drop table #Tmp_Tbl
CREATE TABLE #Tmp_Tbl (
NOT NULL,
AwokeDate datetime,--提醒日期
BeginDate datetime,--开始日期
EndDate datetime,--结束日期
sPrice numeric(18,2),--支付租金数字
TrueDay int,--实际租住天数
DayPrice numeric(4,2),--日租金
MonthNumber int,--整月个数
ExtraDays int, --不满整月的天数
MonthDays int ) --本月天数
INSERT INTO#Tmp_TblSelect * 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)
页:
[1]
