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