高手帮我看看一段超长的存储过程,分不够再加

Question

/*存储过程中很多我已经注释,实在是写的太长,而本人刚刚写存储过程不久,对于这么长的存储过程看起来实在吃力,请高手帮忙看看,或者指点一下看存储过程的技巧,人事系统中有很多问题就是这个引起的,希望大家帮帮忙,不然全厂几百号人可能都会受影响,分不够再加说明:段存储过程是人事系统中的审卡的存储过程*/
/*
將CheTemp中的數據對應工作時間放入chemastertemp中
*/
ALTER PROCEDURE MoveTempDataToMasterTemp_DSH2
@workday varchar(15)
 AS
set nocount on
--declare @workday varchar(15)
if @workday=''
set @workday=convert(varchar(15),getdate()-1,111)

declare @DpID varchar(15)
,@worktype int
,@workonflag varchar(15) --为'on'或者'aon'
,@workoffflag varchar(15) --为'off'或者为'aoff'

  declare @workid int
  ,@starttime varchar(15)
  ,@startint int
  ,@endtime varchar(15)
  ,@endint int
 
  ,@onworktime varchar(20)
  ,@offworktime varchar(20)
 
  ,@workon varchar(30)
  ,@workoff varchar(30)
 
  ,@MaxTime varchar(30)
  ,@TotalTime int
  ,@MinOffworktime varchar(30)
 
  ,@SetLateTime int
  ,@worklate varchar(30)
  ,@lateint int
  ,@workbefore varchar(30)
  ,@beforeint int
 
  ,@LastOffTime varchar(50)
  ,@LastOffTimeRecord varchar(50)

--取得员工信息
select distinct w.*,m.[name]  into #worker  from personnel.dbo.PerWorkData w,personnel.dbo.PerDp d,personnel.dbo.permaster m 
where w.DpID like d.DpID+'%'  and m.workid=w.workid and m.enable=1
--and d.dpgroupid=@groupid
--select * from #worker

--取得最大迟到时间的时间值（为15）
select @setlatetime=parametervalue from sysglobal.dbo.syssetparameter where valueid='LateTime'

--删除CheMasterTemp表中指定员工今天的考勤记录
delete CheMasterTemp
from #worker w,Chemastertemp t
where w.workid=t.workid and t.workday=@workday
---取工作時間
create table #timemm(workid int ,FromDate varchar(15),todate varchar(15),typeid varchar(15),starttime varchar(10),startint int,endtime varchar(15),endint int ,totaltime float,
worktype int, flag int)
insert into #timemm
exec CHEFindAllWorkTime '',@workday,2/*=====================*/
--把#timemm的记录重新整理一下
select * into #time from #timemm order by workid,startint,starttime asc
drop table #timemm
/*=====================*/
--////////////////////////////////////////////////////////////
--select * from #time
---///////統計每人工作時間和實際上班時間
--create table #worktimes(workid int,workint int,recordint int) create table #show(workid int,worktime varchar(30),recordtime varchar(30),types varchar(15))
create table #record(workid int,workday datetime,recordtime varchar(15),worktime varchar(30))--,difftime int,workflag varchar(15)

--把记录整理放进#record表中
insert into #record
select distinct p.workid,p.workday,p.recordtime,convert(varchar(15),p.workday,111)+' '+p.recordtime worktime--,datediff(mi,(convert(varchar(15),workday,111)+' '+recordtime),@onworktime) difftime,'on'
from chetemp p,#time t where p.workid=t.workid and workday between cast(@workday as datetime) and cast(@workday as datetime)+1
union
select distinct p.workid,p.workday,p.recordtime,convert(varchar(15),p.workday,111)+' '+p.recordtime worktime--,datediff(mi,(convert(varchar(15),workday,111)+' '+recordtime),@onworktime) difftime,'on'
from CheTempBackup p,#time t 
where p.workid=t.workid and workday between cast(@workday as datetime) and cast(@workday as datetime)+1
and cast(p.workid as varchar(15))+convert(varchar(15),p.workday,111)+p.RecordTime not in (select cast(workid as varchar(15))+convert(varchar(15),workday,111)+RecordTime from  chetemp where workday between cast(@workday as datetime) and cast(@workday as datetime)+1 )

while exists(select * from #time where flag=1)
begin

--取得最上面的一条记录
select top 1 @workid=workid,@starttime=starttime,@startint=startint,@endtime=endtime,@endint=endint,@worktype=worktype  from #time where flag=1
--取得正班结束时间（哪天几点几分）
select @MaxTime=max(convert(varchar(20),cast(@workday as datetime)+endint,111)+' '+endtime) from #time where workid=@workid and worktype=0 --and  flag=1
--select @totaltime=sum(datediff(mi,cast(@workday+' '+@starttime as datetime)+@startint,cast(@workday+' '+@endtime as datetime)+@endint)) from #time where workid=@workid and worktype=0 group by workid
--取得正班上班时间（按分钟统计）
select @totaltime=sum(totaltime) from #time where workid=@workid and worktype=0 group by workid

if @worktype=0  --如果正班
begin
set @workonflag='on'
set @workoffflag='off'
end
else --否则为加班
begin
set @workonflag='aon'
set @workoffflag='aoff'
end

--set @onworktime=perdaily.dbo.formatDateString(convert(varchar(30),cast(@workday+' '+@starttime as datetime)+@startint,21))
--取得开始上班时间
set @onworktime=convert(varchar(15),cast(@workday as datetime)+@startint,111)+' '+@starttime
--set @offworktime=perdaily.dbo.formatDateString(convert(varchar(30),cast(@workday+' '+@endtime as datetime)+@endint,21))
--取得下班时间
set @offworktime=convert(varchar(15),cast(@workday as datetime)+@endint,111)+' '+@endtime
set @workon=''
set @workoff=''
--？？不懂
select @LastOffTime=isnull(max(convert(varchar(20),cast(@workday as datetime)+endint,111)+' '+endtime),'') from #time where workid=@workid and convert(varchar(20),cast(@workday as datetime)+endint,111)+' '+endtime<=@onworktime
if @totaltime>480
set @MinOffworktime=perdaily.dbo.formatDateString(convert(varchar(30),dateadd(mi,480-@totaltime,@offworktime),21))
               select top 1 @workon=worktime from #record where workid=@workid and abs(datediff(mi,worktime,@onworktime))<240  order by abs(datediff(mi,worktime,@onworktime)) select top 1 @workoff=worktime from #record where workid=@workid and worktime<>@workon  and abs(datediff(mi,worktime,@offworktime))<240  order by abs(datediff(mi,worktime,@offworktime))
---對找到的時間做調整
                                                if @workon<>'' 
begin
if @workon>dateadd(mi,-30,@offworktime)
begin
set @workoff=@workon
set @workon=''
end
end

Answer 1

if @workonflag ='aon' and @workoffflag='aoff'
begin
if @workon<>'' and @workoff='' and datediff(mi,@onworktime,@workon)>30 
begin
set @workoff=@workon
set @workon=''
end 
end    /*===============*/
set @LastOffTimeRecord=''
/*===============*/
if @LastOffTime<>''   ---對出現中午隻有一次刷卡的情況的調整
begin
select top 1 @LastOffTimeRecord=recordtime from #show where workid=@workid and worktime=@lastofftime
if @onworktime=@lastofftime and @workon='' and @lastofftimerecord<>'' and  datediff(mi,@onworktime,cast(@LastOffTimeRecord as datetime))>=30 and @workonflag<>'aon'
begin
set @workoff=@LastOffTimeRecord
set @workon=@onworktime
update #show set recordtime=worktime where workid=@workid and worktime=@LastOffTime 
--
update chemastertemp set recordtime=worktime where workid=@workid and worktime=@LastOffTime
end
if @onworktime=@lastofftime and  @lastofftimerecord<>''  and @workonflag='aon' and  datediff(mi,@onworktime,cast(@LastOffTimeRecord as datetime))>0
begin
set @workoff=@LastOffTimeRecord
set @workon=@onworktime
update #show set recordtime=worktime where workid=@workid and worktime=@LastOffTime 
--
update chemastertemp set recordtime=worktime where workid=@workid and worktime=@LastOffTime
end if @onworktime>@lastofftime and  @lastofftimerecord<>'' and @workoff='' and @workon='' and @workonflag='aon' and  datediff(mi,@onworktime,cast(@LastOffTimeRecord as datetime))>30
begin
set @workoff=@LastOffTimeRecord
--set @workon=@onworktime
update #show set recordtime='',types='nowork' where workid=@workid and worktime=@LastOffTime 
--
update chemastertemp set recordtime='',workstatus='nowork' where workid=@workid and worktime=@LastOffTime
end
/*===kubou 2005-08-23  為處理下午放假審卡時錯位，將打卡時間與設定時間差大於50分種=========*/
/*====會對早上遲到產生影響，當遲到大於50分鐘後才算礦工。==========*/
if @lastofftimerecord<>'' and @workon='' and datediff(mi,cast(@LastOffTime as datetime),@lastofftimerecord)>30 and @workonflag<>'aon'
begin
set @workon=@lastofftimerecord
update #show set recordtime='' ,types='nowork' where workid=@workid and worktime=@LastOffTime
--
update chemastertemp set recordtime='',workstatus='nowork' where workid=@workid and worktime=@LastOffTime
end
/*========================================================================*/
--注意：中途請假需手動調整。
if @lastofftimerecord<>'' and @workon<>'' and @workoff='' and @workon>=@MinOffworktime and @workonflag='on'
begin
if abs(datediff(mi,cast(@onworktime as datetime),@lastofftimerecord))<=abs(datediff(mi,cast(@lastofftime as datetime),@lastofftimerecord))
begin
set @workoff=@workon
set @workon=@lastofftimerecord
update #show set recordtime='' ,types='nowork' where workid=@workid and worktime=@LastOffTime
--
update chemastertemp set recordtime='',workstatus='nowork' where workid=@workid and worktime=@LastOffTime 
end
else
begin
set @workoff=@workon
set @workon=''
end
end
---中午直落且提前下班的情況
if @LastOffTimeRecord='' and @workon<>'' and @workoff='' and @workon>=@MinOffworktime  and @workonflag='on'
begin
set @workoff=@workon
set @workon=''
end
end     
                                                                                                                                                         
--=`=`=`=`=`=`=`=`=`=遲到，且不超過規定遲到限制時間
if @workon<>''
begin
       
       if datediff(mi,cast(@onworktime as datetime),@workon)>0 
       begin
if datediff(mi,cast(@onworktime as datetime),@workon)<=@setlatetime
begin

insert into #show
  values(@workid,@onworktime,@workon,'later')
 --delete #record where workid=@workid and worktime=@workon
--
insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@onworktime,@workon,@workonflag,'later',null,getdate(),1)
delete #record where workid=@workid and worktime=@workon
end
else
begin
insert into #show
  values(@workid,@onworktime,@workon,'laternowork')
--
insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@onworktime,@workon,@workonflag,'laternowork',null,getdate(),1)
end
         end
         else
         begin
if @workonflag='aon'
begin
insert into #show
values(@workid,@onworktime,@onworktime,'normal')
 --
 insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@onworktime,@onworktime,@workonflag,'normal',null,getdate(),1)
end
else
begin
insert into #show
values(@workid,@onworktime,@workon,'normal')
--
 insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@onworktime,@workon,@workonflag,'normal',null,getdate(),1)
end
         end
         delete #record where workid=@workid and worktime=@workon
 end    
 else
begin
insert into #show
  values(@workid,@onworktime,'','nowork')
--
insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@onworktime,'',@workonflag,'nowork',null,getdate(),1)
end
--=`=`=`=`=`=`=`=`=`=早退
if @workoff<>''
begin       if datediff(mi,@workoff,cast(@offworktime as datetime))>0
      begin
              if cast(@MaxTime as datetime)=cast(@offworktime as datetime) and @totaltime>480 
              begin ---工作時間滿8小時不計早退

set @MaxTime=convert(varchar(20),dateadd(mi,480-@totaltime,@MaxTime),111)+' '+convert(varchar(5),dateadd(mi,480-@totaltime,@MaxTime),108)
if @workoff>=@MaxTime  
begin
insert into #show
  values(@workid,@offworktime,@workoff,'normal')
        insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@offworktime,@workoff,@workoffflag,'normal',null,getdate(),1)
--- //將正班裡的加班加到CheAddRecordTemp中
insert into chemastertemp(workid,workday,RecordTime,Worktime,WorkFlag,WorkStatus,createdate,enable)
values(@workid,@workday,@MaxTime,@MaxTime,'aon','normaladd',getdate(),1)
--
insert into chemastertemp(workid,workday,RecordTime,Worktime,WorkFlag,WorkStatus,createdate,enable)
values(@workid,@workday,@workoff,@workoff,'aoff','normaladd',getdate(),1)

end

Answer 2

else
begin
if datediff(mi,cast(@offworktime as datetime),@workoff)<=@setlatetime
begin
insert into #show
  values(@workid,@offworktime,@workoff,'before')
--
insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@offworktime,@workoff,@workoffflag,'before',null,getdate(),1)
end
else
begin
insert into #show
  values(@workid,@offworktime,@workoff,'beforenowork')
insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@offworktime,@workoff,@workoffflag,'beforenowork',null,getdate(),1)
end
end
            end
            else
            begin
insert into #show
  values(@workid,@offworktime,@workoff,'before')
--
insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@offworktime,@workoff,@workoffflag,'before',null,getdate(),1)

            end
       end
       else
       begin
if @workoffflag='aoff'  and  datediff(mi,@offworktime,@workoff)>=30
begin
insert into #show
  values(@workid,@offworktime,@offworktime,'normal')
        --
        insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@offworktime,@offworktime,@workoffflag,'normal',null,getdate(),1)
end
else
begin
insert into #show
  values(@workid,@offworktime,@workoff,'normal')
        --
        insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@offworktime,@workoff,@workoffflag,'normal',null,getdate(),1)
end
        end
        delete #record where workid=@workid and worktime=@workoff
end
else
begin
insert into #show
  values(@workid,@offworktime,'','nowork')
--
insert into chemastertemp(workid,workday,worktime,recordtime,workflag,workstatus,createuser,createdate,enable)
values(@workid,@workday,@offworktime,'',@workoffflag,'nowork',null,getdate(),1)
end update #time set flag=0 where workid=@workid and starttime=@starttime and startint=@startint
--delete #show
end
---select * from chetemp where workday=@workday
/*對曠工的情況進行處理*/
--delete #show where types='nowork' and exists(select * from worklot where worklot.workid=#show.workid and #show.worktime between Fromdate and case when len(todate)<11 then cast(todate as datetime)+1 else todate end  and lottype in ('holiday','leave','adjustleave','industrialinjury','askleave','outwork','zl','signcard') and worklot.enable=1)
--select s.workid,worktime,recordtime,types,w.[name] from #show s  left join #worker w  on w.workid=s.workid  order by s.workid,worktime
--select workid,right(worktime,5) worktime,right(recordtime,5) recordtime,types from #show order by workid,worktime

---將CheTemp的數據轉移到CheTempBackup中
--
insert into CheTempBackup
select  c.*,getdate() from chetemp c,#worker w where c.workid=w.workid and c.workday=@workday

--删除CheTemp表中已经转移的数据
delete CheTemp
from chemastertemp s,chetemp c
where c.workid=s.workid and c.workday=s.workday and  c.workday=@workday and s.recordtime=convert(varchar(15),c.workday,111)+' '+c.recordtime

-----休假加班
insert into AddLot(workid,workday,recordtime,worktime,worktype,createdate,newtype,enable)
select r.workid,convert(varchar(15),c.workday,111),c.recordtime,c.worktime,'xiujia',getdate(),w.lottype,2
from worklot w,#worker r,chemastertemp c 
where w.workid=r.workid and c.workid=r.workid and c.workday=@workday and c.workstatus='normal' 
and (lottype='leave' or lottype='holiday' or lottype='adjustleave') --and convert(varchar(15),cast(fromdate as datetime),111)=@workday
and c.worktime between case when c.workday<convert(varchar(15),cast(c.worktime as datetime),111) then cast(w.fromdate as datetime)+1 else cast(w.fromdate as datetime) end  and   case when c.workday<convert(varchar(15),cast(c.worktime as datetime),111) then cast(w.todate as datetime)+2 else  case when len(w.todate)>10 then cast(w.todate as datetime) else cast(w.todate as datetime)+1 end end
--and convert(varchar(15),r.workid)+convert(varchar(15),c.workday,111)+c.recordtime+c.worktime not in (select cast(workid,varchar(15)+convert(varchar(15),workday,111)+recordtime+worktime  from AddLot where workid=r.workid and worktype='xiujia'  ) create table #MoreRecord(num int IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL,workid varchar(15),[name] nvarchar(50),workday varchar(30),recordtime varchar(30))
insert into #MoreRecord(workid,name,workday,recordtime)
select  c.workid,w.[name],c.workday,convert(varchar(15),c.workday,111)+' '+c.recordtime
from chetemp c,#worker w ,(select workid,workday,min(worktime) MinTime,max(worktime) MaxTime from chemastertemp where workday=@workday  group by workid,workday) m
where c.workid=w.workid  and m.workid=c.workid --and m.workday=c.workday and c.workday=@workday
and (( cast(c.workday+' '+c.recordtime as datetime)>m.maxtime and datediff(mi,m.maxtime,cast(c.workday+' '+c.recordtime as datetime))>30) or (cast(c.workday+' '+c.recordtime as datetime)<dateadd(mi,-30,cast(m.MinTime as datetime))  and cast(c.workday+' '+c.recordtime as datetime)>dateadd(hh,-4,cast(m.MinTime as datetime)) ))
and   cast(c.workday+' '+c.recordtime as datetime)<dateadd(hh,-4,cast(m.mintime as datetime)+1)
and  cast(c.workday+' '+c.recordtime as datetime)>dateadd(hh,5,cast(m.MaxTime as datetime)-1)
order by c.workid,c.workday,recordtime ---多上班
insert into AddLot(workid,workday,recordtime,worktime,worktype,createdate,enable)
select workid,@workday,addfrom,addto,'morework',getdate(),2
from (select workid,name,recordtime addfrom,isnull((select min(b.recordtime) from #morerecord b where b.workid=a.workid and b.recordtime>a.recordtime),'') addto,
(select count(*) from #morerecord c where c.workid=a.workid and c.recordtime<=a.recordtime) cnt from #morerecord a) tt
where cnt%2<>0
--下班嚴重超時
insert into AddLot(workid,workday,worktime,recordtime,worktype,createdate,enable)
select c.workid,convert(varchar(15),c.workday,111),c.worktime,c.recordtime,'outtime',getdate(),2 from chemastertemp c ,#worker w
where c.workid=w.workid and c.workday=@workday and (c.workflag='off' or c.workflag='aoff') 
and datediff(mi,c.worktime,c.recordtime)>60 and (c.workstatus='normal' or c.workstatus='before')
---取有加班設定未刷卡                        
insert into AddLot(workid,workday,worktime,recordtime,worktype,createdate,enable)
select c.workid,convert(varchar(15),c.workday,111),c.worktime,'','noaddwork',getdate(),2  from chemastertemp c,#worker w  where workday=@workday and c.workid=w.workid and workstatus='nowork'  and (workflag='aon' or workflag='aoff') order by c.workid,worktime
insert into worklot(workid,fromdate,todate,lottype,totaltime,ctype,dtype,loginid,createdate,enable)
select c.workid,c.worktime,'','nowork',0,convert(varchar(15),c.workday,111),1,'',getdate(),1  from chemastertemp c,#worker w  where workday=@workday and c.workid=w.workid and workstatus='nowork'  and (workflag='aon' or workflag='aoff') order by c.workid,worktime
drop table #worker,#time,#record,#show,#MoreRecord

Answer 3

谢谢各位啦,是很长呀,在这上面看是不可能看的好的,最好在工具里面看才比较直接,或者各位提供点看这种SQL语句的技巧也可以,我实在是被中间的while或if语句搞晕啦

Answer 4

这是一个审卡程序，最初的开发者不在，现在留下这个有点问题的审卡程序，看的我晕晕的，真是令人头大，命苦呀，
而且没有注释，里面一些流程是我自己加上去的，其他的单独的语句还好说，就是其中的 while里面的不知道多少个的if  begin end 都不知道哪里开始，哪里结束