一段SQL语句（涉及游标）

--建立临时表

create table #People
(
pname nvarchar(30)
)

insert #People values('4479')
insert #People values('542')
insert #People values('543')

declare @randdate datetime,@randdate1 datetime, @count int,@sdate  datetime,@edate datetime,@sdate1  datetime,@edate1 datetime,@pname char(5),@Nowdate datetime

--set @count=(select count(*) from  yskqnew where sj>(select convert(varchar(10),getdate(),120)) and bh='04479')
--定义随机时间
 set   @sdate   =   '07:50:00'  
 set   @edate   =   '07:58:00'
 set   @sdate1  =   '17:05:00'
 set   @edate1  =   '17:10:00'
 set   @Nowdate= (select convert(varchar(10),getdate(),120))

 

--生成当天日期+随机时间
set @randdate=(select convert(varchar(10),getdate(),120)+ dateadd(n,rand()*(datediff(n,@sdate,@edate)),@sdate))

set @randdate1=(select convert(varchar(10),getdate(),120)+ dateadd(n,rand()*(datediff(n,@sdate1,@edate1)),@sdate1))

 

--创建游标，逐行取临时表中数据

declare Pname_Cursor  cursor
for select pname from #People where pname in('4479','542','543')
for read only
open Pname_Cursor
begin
fetch next from Pname_Cursor into @pname
while(@@fetch_status=0)
begin

--取当天已insert数据
set @count=(select count(*) from  yskqnew where sj>(select convert(varchar(10),getdate(),120)) and bh=@pname)
--有数据时删除已有数据并insert自定义数据，无数据时直接insert自定义数据
if(@count<>0)
begin --判断表中是否存在加班数据，若没有，执行delete&insert,若有则只delete上午刷卡数据，保留加班刷卡数据，并insert自定义8:00刷卡数据
     if not exists(select * from yskqnew where bh=@pname and sj>(select convert(varchar(10),getdate(),120)+' 18:00:00'))
     begin
          delete from yskqnew where bh=@pname and sj>(select convert(varchar(10),getdate(),120));
          insert into yskqnew (bh,rq,sj,jh,bc) values (@pname,@Nowdate,@randdate,1,'1');
          insert into yskqnew (bh,rq,sj,jh,bc) values (@pname,@Nowdate,@randdate1,1,'1');
     end
     else
     begin
     delete from yskqnew where bh=@pname and sj>(select convert(varchar(10),getdate(),120)+' 18:00:00');
     insert into yskqnew (bh,rq,sj,jh,bc) values (@pname,@Nowdate,@Nowdate+' 22:02:00',1,'1');
     end
end
else
begin
insert into yskqnew (bh,rq,sj,jh,bc) values (@pname,@Nowdate,@randdate,1,'1');
insert into yskqnew (bh,rq,sj,jh,bc) values (@pname,@Nowdate,@randdate1,1,'1')
end

fetch next from Pname_Cursor into @Pname
end
close Pname_Cursor
deallocate Pname_Cursor
end
truncate table #People--断开并删除临时表
drop table #People