CREATE PROCEDURE `spAddTimeTable_Validation` ( IN `P_BatchId` INTEGER , IN `P_TimeTableXml` LONGTEXT , IN `P_TimeTableId` INTEGER , IN `P_Day` VARCHARACTER (?) , IN `P_FromTime` TIME , IN `P_ToTime` TIME , IN `P_FromDate` DATE , IN `P_IsSession` TINYINT , IN `P_Title` LONGTEXT , IN `P_AnouncementMessage` LONGTEXT , IN `P_Subject` VARCHARACTER (?) ) BEGIN DECLARE `Count` INTEGER DEFAULT ? ; DECLARE `i` INTEGER DEFAULT ? ; DECLARE `DayCount` INTEGER DEFAULT ? ; IF ( `P_IsSession` = ? ) THEN SET `Count` = `ExtractValue` ( `P_TimeTableXml` , ? ) ; WHILE `i` <= `Count` DO IF ( `ExtractValue` ( `P_TimeTableXml` , `CONCAT` ( ? , `i` , ? ) ) = ? ) THEN SET `DayCount` = ( SELECT COUNT ( * ) FROM `ttimetable` WHERE SQL_TSI_DAY = `ExtractValue` ( `P_TimeTableXml` , `CONCAT` ( ? , `i` , ? ) ) AND `BatchId` = `P_BatchId` AND `IsSession` = ? ) ; IF ( `DayCount` < ? ) THEN IF EXISTS ( SELECT ? FROM `ttimetable` WHERE SQL_TSI_DAY = `ExtractValue` ( `P_TimeTableXml` , `CONCAT` ( ? , `i` , ? ) ) AND `BatchId` = `P_BatchId` AND ( ( CAST ( `ExtractValue` ( `P_TimeTableXml` , `CONCAT` ( ? , `i` , ? ) ) AS TIME ) BETWEEN `FromTime` AND `ToTime` ) OR ( CAST ( `ExtractValue` ( `P_TimeTableXml` , `CONCAT` ( ? , `i` , ? ) ) AS TIME ) BETWEEN 