CREATE PROCEDURE `spDeleteTest` ( `P_TestId` INTEGER ) BEGIN DECLARE `QuestionCount` INTEGER DEFAULT ? ; DECLARE `OptionCount` INTEGER DEFAULT ? ; IF EXISTS ( SELECT ? FROM `tetest` `T` WHERE CASE WHEN `IFNULL` ( `T` . `ExpiryDate` , ? ) = ? THEN ( ( `T` . `StartTime` + INTERVAL `T` . `Duration` SQL_TSI_MINUTE < `TIME_FORMAT` ( CURTIME ( ) , ? ) AND `T` . `StartDate` = CURDATE ( ) ) OR ( `T` . `StartDate` < CURDATE ( ) ) ) ELSE ( ( `T` . `ExpiryTime` < `TIME_FORMAT` ( CURTIME ( ) , ? ) AND `T` . `ExpiryDate` = CURDATE ( ) ) OR ( `T` . `ExpiryDate` < CURDATE ( ) ) ) END AND `T` . `IsSubmitted` = ? AND `T` . `id` = `P_TestId` ) THEN SELECT ? AS `Result` ; ELSE BEGIN START TRANSACTION ; SET `QuestionCount` = ( SELECT COUNT ( `Id` ) FROM `tetestquestions` WHERE `TestId` = `P_TestId` GROUP BY `TestId` ) ; IF ( `QuestionCount` >= ? ) THEN BEGIN CREATE TEMPORARY TABLE `QuestionIdTable` SELECT `Id` , `FilePath` FROM `tetestquestions` WHERE `TestId` = `P_TestId` ; CREATE TEMPORARY TABLE `OptionIdTable` SELECT `tto` . `Id` , `tto` . `FilePath` FROM `tetestquestionoptions` `tto` INNER JOIN `tetestquestions` `ttq` ON `ttq` . `Id` = `tto` . `QuestionId` WHERE `TestId` = `P_TestId` ; DELETE `tqo` FROM `tetestquestionoptions` `tqo` INNER JOIN 