DELIMITER $$
CREATE  PROCEDURE `TeacherGeneratedCaIdProcedure` (IN `user_type` INT, IN `institute_eiin` VARCHAR(20), IN `joining_year` INT, OUT `generated_CAID` BIGINT)   BEGIN
  DECLARE CAID BIGINT;
  DECLARE sequential_increment INTEGER;

  SELECT IFNULL(CONCAT(user_type, institute_eiin, joining_year), 0) INTO CAID
  FROM `user_accounts`
  WHERE `user_type_id` = user_type AND `eiin` = institute_eiin AND `year` = joining_year ORDER By id DESC  limit 1;

  IF CAID IS NULL THEN
    SET generated_CAID = CONCAT(user_type, institute_eiin, joining_year, '0001');
  ELSE
    SELECT sequential + 1 INTO sequential_increment
    FROM `user_accounts`
    WHERE `user_type_id` = user_type AND `eiin` = institute_eiin AND `year` = joining_year ORDER By id DESC  limit 1;

    SET generated_CAID = CONCAT(user_type, institute_eiin, joining_year, LPAD(sequential_increment, 4, '0'));
  END IF;
      SELECT generated_CAID;
END$$

DELIMITER ;