51Testing软件测试论坛

标题: 求助sql问题，如何在存储过程中调用函数 [打印本页]

作者: 悠悠小仙仙 时间: 2017-6-21 13:42
标题: 求助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 时间: 2017-6-21 15:32
存储过程：
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

复制代码

作者: 乐哈哈yoyo 时间: 2017-6-21 15:34

if object_id('tempdb..#Tmp_Tbl') is not null
drop table #Tmp_Tbl
CREATE TABLE #Tmp_Tbl (
[id] [int] 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_Tbl Select * From dbo. dbo.Pub_FN_FundsList(@d1,@d2,@sPrice,@Term,@AheadDays,@Align)

复制代码

作者: 八戒你干嘛 时间: 2017-6-21 15:35
直接在过程中：
Select * From dbo. dbo.Pub_FN_FundsList('2017-06-17','2017-07-18',3000, 3, 5, 0)

欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/)