ALTER PROCEDURE [dbo].[proc_AdvReportMutSelect]
@VacationDate datetime,
@dateDiff int
AS
declare @i int
set @i=0
WHILE (@i<@dateDiff)
BEGIN
select
b.CIFNm,b.CustomNm,dd.phone,(SUM(isnull(a.RepayAmount-a.receivableAmount,0))) Repaymoney,
kk.PRepayDate,
b.LoanNm,b.LoanStratDate,b.LoanAmount
from
(
select * from InterestReceive
union all select * from InterestReceivetemp
) a
left join
(
select max(convert(varchar(10),PeriodRepyDate,120)) PRepayDate,ContractNm repd from
(
select * from InterestReceive
union all select * from InterestReceivetemp) aa
group by ContractNm
)
kk
on kk.repd=a.ContractNm
left join OPSInf b on a.ContractNm=b.LoanNm
left join (
select CIFNm,phone from
(
select CIFNm,case LoanType when '个人客户' then
case
when SUBSTRING(PerMobliePhB,0,PATINDEX('%|%',PerMobliePhB))='' then PerMobliePhB
else SUBSTRING(PerMobliePhB,0,PATINDEX('%|%',PerMobliePhB))
end
when '企业客户' then CompanyContactMphontA else '' end phone,SalesDate
from LoanInf where Followstate='已审核'
) c
left join
(select MAX(salesdate) salsd ,CIFNm cfin from LoanInf where
Followstate='已审核' group by CIFNm) b
on
b.cfin=c.CIFNm
where salsd = c.SalesDate
) dd
on dd.CIFNm=a.CIFNm
where convert(varchar(10),a.PeriodRepyDate,120)=convert(varchar(10),dateadd(day,@i,@VacationDate),120)
and isnull(a.RepayAmount-a.receivableAmount,0)>0
group by
b.CIFNm,b.CustomNm, dd.phone,kk.PRepayDate,b.LoanNm,b.LoanStratDate,b.LoanAmount
SET @i=@i+1
END
这个存储过程是正确的，但是当我按照红色部分写也是正确的，唯一有问题的地方就是程序读的时候，只能读取到第一条循环记录的值，后面的就读不出来了，奇怪，有人知道么》
比如：
当赋值： @VacationDate ‘2013-12-20’,
@dateDiff 7
这个时候，运行代码，就只能读到1天的数据，但是在SQLSERVER编辑器里测试，就可以读到七天，这个是MSSQLbug么》
------解决方案--------------------
帮你改了一下，你再试试：
ALTER PROCEDURE [dbo].[proc_AdvReportMutSelect]
@VacationDate datetime,
@dateDiff int
AS
declare @i int
set @i=0
WHILE (@i<@dateDiff)
BEGIN
select
b.CIFNm,b.CustomNm,dd.phone,(SUM(isnull(a.RepayAmount-a.receivableAmount,0))) Repaymoney,
kk.PRepayDate,
b.LoanNm,b.LoanStratDate,b.LoanAmount
from
(
select * from InterestReceive
union all select * from InterestReceivetemp
) a
left join
(
select max(convert(varchar(10),PeriodRepyDate,120)) PRepayDate,ContractNm repd from
(
select * from InterestReceive
union all select * from InterestReceivetemp) aa
group by ContractNm
)
kk
on kk.repd=a.ContractNm
left join OPSInf b on a.ContractNm=b.LoanNm
left join (
select CIFNm,phone from
(
select CIFNm,case LoanType when '个人客户' then
case
when SUBSTRING(PerMobliePhB,0,PATINDEX('%
------解决方案--------------------
%',PerMobliePhB))='' then PerMobliePhB
else SUBSTRING(PerMobliePhB,0,PATINDEX('%
------解决方案--------------------
%',PerMobliePhB))
end
when '企业客户' then CompanyContactMphontA else '' end phone,SalesDate
from LoanInf where Followstate='已审核'
) c
left join
(select MAX(salesdate) salsd ,CIFNm cfin from LoanInf where
Followstate='已审核' group by CIFNm) b
on
b.cfin=c.CIFNm
where salsd = c.SalesDate
) dd
on dd.CIFNm=a.CIFNm
where convert(varchar(10),a.PeriodRepyDate,120)=convert(varchar(10),dateadd(day,@i,@VacationDate),120)
and isnull(a.RepayAmount-a.receivableAmount,0)>0
group by
b.CIFNm,b.CustomNm, dd.phone,kk.PRepayDate,b.LoanNm,b.LoanStratDate,b.LoanAmount
SET @i=@i+1
--修改了这里，每次都把@VacationDate 增加1天
set @VacationDate= DATEADD(day,1,@VacationDate)
END
------解决方案--------------------
if object_id('t') is not null
drop table t;
create table t (id int identity(1,1),edate date)
go
insert into t