if exists(select *from sysobjects where name='sp_contestsubmit')
drop proc sp_contestsubmit
go
-- =============================================
-- author: zqt
-- create date: 2011-11-25
-- desc: 系统管理员批量给未交卷的考生交卷-竞赛
-- =============================================
create proc sp_contestsubmit
@groupid int --考核id
as
--申明一个游标
declare mycursor cursor
for select pk_userexamid,fk_userid,fk_examorcontestid from score_userexam where iscomplete=0 and fk_examorcontestid in(select pk_contestinfoid from contest_info where fk_contestgroupid=@groupid)
--打开一个游标
open mycursor
--循环一个游标
declare @userexamid int ,@userid int ,@exammanageid int
fetch next from mycursor into @userexamid, @userid,@exammanageid
while @@fetch_status =0
begin
--获取考试考试分数
declare @userexamscore int
select @userexamscore=sum(score) from score_useranswer where fk_userid=@userid and fk_userexamid=@userexamid
--修改考生交卷信息
update [score_userexam] set [score] =@userexamscore,[iscomplete] = 1,[endtime] = getdate(),[durationsecs] = datediff(ss,begintime,getdate()) where pk_userexamid=@userexamid
fetch next from mycursor into @userexamid, @userid,@exammanageid
end
--关闭游标
close mycursor
--释放资源
deallocate mycursor
/* 测试
exec sp_contestsubmit 1
*/
go