CREATE DEFINER = `root` @`%` PROCEDURE `spAlertGetInstitute` ( IN `P_Template` VARCHARACTER (?) , IN `P_MailSubject` VARCHARACTER (?) , IN `P_InstituteId` INTEGER , IN `P_StudentId` INTEGER ) BEGIN SELECT `I` . `Id` , `I` . `Title` , `S` . `Id` , `U` . `MobileNo` , `IFNULL` ( `S` . `EmailId` , ? ) AS `EmailId` , REPLACE ( REPLACE ( `P_Template` , ? , `I` . `Title` ) , ? , `U` . `Name` ) AS `MsgText` , `P_MailSubject` AS SUBJECT , ? AS `ReceiverRoleTypeId` , `T` . `Id` AS `ReceiverSourceId` , `T` . `UserId` AS `UserId` , `T` . `UserId` AS `ReceiverUserId` , `T` . `InstituteId` AS `InstituteId` , `B` . `Id` AS `BatchId` , `B` . `Title` AS `BatchName` FROM `tinstitute` `I` LEFT JOIN `tteacher` `T` ON `T` . `InstituteId` = `I` . `Id` LEFT JOIN `tbatch` `B` ON `B` . `Id` = `T` . `BatchId` LEFT JOIN `tstudent` `S` ON `S` . `InstituteId` = `I` . `Id` LEFT JOIN `tuser` `U` ON `S` . `UserId` = `U` . `Id` LEFT JOIN `tuser` `U1` ON `T` . `UserId` = `U1` . `Id` WHERE ( `I` . `Id` = `P_InstituteId` OR `P_InstituteId` = ? ) AND ( `S` . `Id` = `P_StudentId` OR `P_StudentId` = ? ) 