CREATE PROCEDURE `spAlertGetHomeworkData` ( IN `P_HomeworkId` INTEGER , IN `P_Template` VARCHARACTER (?) , IN `P_TeacherId` INTEGER , IN `P_BatchId` INTEGER , IN `P_MailSubject` VARCHARACTER (?) , IN `P_HomeworkStatusId` INTEGER , IN `P_RoleTypeId` INTEGER ) BEGIN IF ( `P_RoleTypeId` = ? ) THEN SELECT `H` . `Id` , `H` . `Title` , `H` . `TeacherId` , `H` . `BatchId` AS `BatchId` , `B` . `Title` AS `BatchName` , `H` . `HomeworkDateTime` , `HA` . `StudentId` , `HA` . `StatusId` , `SM` . `Title` AS `StatusTitle` , `U` . `MobileNo` , `IFNULL` ( `S` . `EmailId` , ? ) AS `EmailId` , REPLACE ( REPLACE ( REPLACE ( REPLACE ( `P_Template` , ? , `B` . `Title` ) , ? , `U1` . `Name` ) , ? , `H` . `Title` ) , ? , `ifnull` ( DATE ( `H` . `HomeworkDateTime` ) , ? ) ) AS `MsgText` , `P_MailSubject` AS SUBJECT , `T` . `UserId` AS `UserId` , ? AS `ReceiverRoleTypeId` , `S` . `Id` AS `ReceiverSourceId` , `S` . `UserId` AS `ReceiverUserId` , `T` . `InstituteId` AS `InstituteId` FROM `thomework` `H` LEFT JOIN `thomeworkassign` `HA` ON `HA` . `HomeworkId` = `H` . `Id` LEFT JOIN `tstatusmaster` `SM` ON `SM` . 