很复杂的SQL存储过程

CREATE PROC lsqlkqzbsj
@xzyf varchar(7),
@sbsj char(10),
@xbsj char(10),
@xxts int,
@zbsj char(10),
@zbsj1 char(10),
@wcsc NVARCHAR(200) OUTPUT
AS
begin
DECLARE @jsnyts int
DECLARE @i int
DECLARE @bm char(10)
DECLARE @xm varchar(20)
DECLARE @sjyf varchar(10)
Declare @time char(10)
DECLARE @sccsjs varchar(200)
Declare @id int
if(@xzyf<>'')
begin
delete from zkqsj where left(convert(char(10),rq,120),7)=@xzyf
begin
select @jsnyts=day(dateadd(d,-Day(dateadd(m,1,convert(datetime,@xzyf+'-01'))),dateadd(m,1,convert(datetime,@xzyf+'-01'))))
set @i=1
Declare qdksj Cursor For Select [PeoNo],[PeoName] From MAdmPeo where left([peono],1)<>'X'
Open qdksj
Fetch Next From qdksj Into @bm,@xm
while @@Fetch_Status=0
begin
begin
Declare @bma char(10)
select @bma=peono from MAdmPeo where peono not in(select bm from zkqsj where bm=@bm and convert(char(10),rq,120)=@sjyf)
if (@bma<>'')
begin
set @i=1
end
end
while @i <= @jsnyts
begin
begin
if @i<10
select @sjyf=@xzyf+'-0'+LTRIM(str(@i))
else
select @sjyf=@xzyf+'-'+LTRIM(str(@i))
end
insert into zkqsj(xm,rq,bm)values(@xm,@sjyf,@bm)
set @i=@i+1
end
Fetch Next From qdksj Into @bm,@xm
end
Close qdksj
DealLocate qdksj
end



begin
Declare @kqdate datetime


Declare qtime1 Cursor For
select distinct [kqdate],[kqtime],[peono],[id] from KqRawList where kqdate in(select distinct kqdate from KqRawList where peono in(select distinct peono from KqRawList where left(peono,1)<>'X'))and left(kqtime,1)='0'and left(convert(char(10),kqdate,120),7)=@xzyf order by id asc
Open qtime1
Fetch Next From qtime1 Into @kqdate,@time,@bm,@id
while @@Fetch_Status=0
begin
update zkqsj set t1=@time where bm=@bm and rq=@kqdate
Fetch Next From qtime1 Into @kqdate,@time,@bm,@id
end
Close qtime1
DealLocate qtime1
end

begin
Declare qtime2 Cursor For
select distinct [kqdate],[kqtime],[peono],[id] from KqRawList where kqdate in(select distinct kqdate from KqRawList where peono in(select distinct peono from KqRawList where left(peono,1)<>'X'))and left(kqtime,1)='1'and left(kqtime,2)>='17'and left(convert(char(10),kqdate,120),7)=@xzyf order by id asc
Open qtime2
Fetch Next From qtime2 Into @kqdate,@time,@bm,@id
while @@Fetch_Status=0
begin
update zkqsj set t2=@time where bm=@bm and rq=@kqdate
Fetch Next From qtime2 Into @kqdate,@time,@bm,@id
end
Close qtime2
DealLocate qtime2
end

begin
Declare qtime3 Cursor For
select distinct [kqdate],[kqtime],[peono],[id] from KqRawList where kqdate in(select distinct kqdate from KqRawList where peono in(select distinct peono from KqRawList where left(peono,1)<>'X'))and left(kqtime,1)='1'and left(kqtime,2)>='17'and left(convert(char(10),kqdate,120),7)=@xzyf order by id desc
Open qtime3
Fetch Next From qtime3 Into @kqdate,@time,@bm,@id
while @@Fetch_Status=0
begin
update zkqsj set t3=@time where bm=@bm and rq=@kqdate
Fetch Next From qtime3 Into @kqdate,@time,@bm,@id
end
Close q

time3
DealLocate qtime3
end

begin
Declare qtime4 Cursor For
select distinct [kqdate],[kqtime],[peono],[id] from KqRawList where kqdate in(select distinct kqdate from KqRawList where peono in(select distinct peono from KqRawList where left(peono,1)<>'X'))and left(kqtime,1)='2'and left(convert(char(10),kqdate,120),7)=@xzyf order by id desc
Open qtime4
Fetch Next From qtime4 Into @kqdate,@time,@bm,@id
while @@Fetch_Status=0
begin
update zkqsj set t4=@time where bm=@bm and rq=@kqdate
Fetch Next From qtime4 Into @kqdate,@time,@bm,@id
end
Close qtime4
DealLocate qtime4
end
set @sccsjs='打卡数据取出'
end


if(@sbsj<>''and @xbsj<>''and @xxts<>'' and @xzyf<>'')
begin
Declare @t1 int
Declare @t2 int
Declare @kg int
begin
delete from 汇总 where rq=@xzyf
insert into 汇总(bm,yh)select PeoNo,PeoName from MAdmPeo where left(peono,1)<>'X'
update 汇总 set rq=@xzyf where rq is null
end
Declare qkqsj Cursor For select [bm] from zkqsj where left(convert(char(10),rq,120),7)=@xzyf
Open qkqsj
Fetch Next From qkqsj Into @bm
while @@Fetch_Status=0
begin
set @t1=(select Count(*) from zkqsj where bm=@bm and left(convert(char(10),rq,120),7)=@xzyf and (t1 is null or t1>@sbsj))
set @t2=(select Count(*) from zkqsj where bm=@bm and left(convert(char(10),rq,120),7)=@xzyf and (t2 is null or t2<@xbsj))
set @kg=(select Count(*) from zkqsj where bm=@bm and left(convert(char(10),rq,120),7)=@xzyf and t1 is null and t2 is null)
update 汇总 set cd=@t1,zt=@t2,kg=@kg,xj=@xxts where rq=@xzyf and bm=@bm
Fetch Next From qkqsj Into @bm
end
Close qkqsj
DealLocate qkqsj
set @sccsjs=@sccsjs+'||考勤数据汇总完成!'
end

if(@zbsj<>''and @zbsj1<>'' and @xzyf<>'')
begin
Declare @t3 int
Declare @t4 int
Declare @zbcs int
begin
delete from 值班 where yf=@xzyf
insert into 值班(bm,yh)select PeoNo,PeoName from MAdmPeo where left(peono,1)<>'X'
update 值班 set yf=@xzyf where yf is null
end
Declare qzbsj Cursor For select [bm] from zkqsj where left(convert(char(10),rq,120),7)=@xzyf
Open qzbsj
Fetch Next From qzbsj Into @bm
while @@Fetch_Status=0
begin
set @t3=(select Count(*) from zkqsj where bm=@bm and left(convert(char(10),rq,120),7)=@xzyf and t3 is not null)
set @t4=(select Count(*) from zkqsj where bm=@bm and left(convert(char(10),rq,120),7)=@xzyf and (t4 is not null or t4>=@zbsj1))
set @zbcs=(select Count(*) from zkqsj where bm=@bm and left(convert(char(10),rq,120),7)=@xzyf and t3 is not null and t4 is not null and t4>=@zbsj1)
update 值班 set zb1=@t3,zb2=@t4,cs=@zbcs where yf=@xzyf and bm=@bm
Fetch Next From qzbsj Into @bm
end
set @sccsjs=@sccsjs+'||值班数据汇总完成!'
Close qzbsj
DealLocate qzbsj
end
if @sccsjs<>''
set @wcsc=@sccsjs
select @wcsc
end



GO