CREATE PROCEDURE `spAddSubmitStudentAnswer` ( `P_AnswerXML` LONGTEXT , `P_TestId` INTEGER , `P_StudentId` INTEGER , `P_ExamDate` DATE , `P_StartTime` TIME , `P_EndTime` TIME ) BEGIN DECLARE `A_Count` INTEGER DEFAULT ? ; DECLARE `R_Count` INTEGER DEFAULT ? ; DECLARE `i` INTEGER DEFAULT ? ; DECLARE `IsCorrect` TINYINT (?) DEFAULT ? ; DECLARE `V_ScoredMarks` DECIMAL (...) DEFAULT ? ; DECLARE `V_TotalMarks` DECIMAL (...) DEFAULT ? ; DECLARE `V_TotalQuestion` INTEGER DEFAULT ? ; DECLARE `V_ObtainMarks` DECIMAL (...) DEFAULT ? ; DECLARE `V_RightAnswer` INTEGER DEFAULT ? ; DECLARE `V_WrongAnswer` INTEGER DEFAULT ? ; DECLARE `V_SkippedAnswer` INTEGER DEFAULT ? ; DECLARE `V_SubTestTypeId` INTEGER ; DECLARE `V_OptionId` INTEGER DEFAULT ? ; DECLARE `V_QuestionId` INTEGER DEFAULT ? ; DROP TEMPORARY TABLE IF EXISTS `AnswerList` ; IF NOT EXISTS ( SELECT ? FROM `tresult` WHERE `TestId` = `P_TestId` AND `StudentId` = `P_StudentId` ) THEN SET `A_Count` = `ExtractValue` ( `P_AnswerXML` , ? ) ; SET `V_TotalMarks` = ( SELECT SUM ( `Marks` ) FROM `tetestquestions` WHERE `TestId` = `P_TestId` ) ; CREATE TEMPORARY TABLE `AnswerList` ( `QuestionId` INTEGER , `Uniquetext` LONGTEXT , `AnswerId` INTEGER ) ; WHILE `i` <= `A_Count` DO SET `V_OptionId` = `ExtractValue` ( `P_AnswerXML` , `CONCAT` ( ? , `i` , ? ) ) ; SET `V_QuestionId` = `ExtractValue` ( `P_AnswerXML` , `CONCAT` ( ? , `i` , ? ) ) 