CREATE PROCEDURE `spAddETestQuestions` ( `P_Id` INTEGER , `P_TestId` INTEGER , `P_QuestionTypeId` INTEGER , `P_Title` LONGTEXT , `P_Marks` DECIMAL (...) , `P_IsSubmitted` BIT , `P_IsSingleAnswer` BIT , `P_ParaId` INTEGER , `P_OptionXML` LONGTEXT ) BEGIN DECLARE `count` INTEGER DEFAULT ? ; DECLARE `i` INTEGER DEFAULT ? ; DECLARE `V_OrderIndex` INTEGER DEFAULT ? ; IF ( `P_Id` = ? ) THEN SET `V_OrderIndex` = ( SELECT `IFNULL` ( MAX ( `OrderIndex` ) + ?, ... ) FROM `tetestquestions` WHERE `TestId` = `P_TestId` ) ; START TRANSACTION ; INSERT INTO `vida_lms` . `tetestquestions` ( `TestId` , `QuestionTypeId` , `Title` , `Marks` , `IsSubmitted` , `IsSingleAnswer` , `ParaId` , `OrderIndex` ) VALUES ( `P_TestId` , `P_QuestionTypeId` , `P_Title` , `P_Marks` , `P_IsSubmitted` , `P_IsSingleAnswer` , `P_ParaId` , `V_OrderIndex` ) ; SET `P_Id` = `LAST_INSERT_ID` ( ) ; COMMIT ; ELSEIF ( `P_Id` > ? ) THEN UPDATE `vida_lms` . `tetestquestions` SET `QuestionTypeId` = `P_QuestionTypeId` , `Title` = `P_Title` , `Marks` = `P_Marks` , `IsSubmitted` = `P_IsSubmitted` , `IsSingleAnswer` = `P_IsSingleAnswer` , `ParaId` = `P_ParaId` WHERE `Id` = `P_Id` ; END IF ; IF ( `P_Id` > ? 