$ cat sql/getBatchCode.sql
DROP PROCEDURE IF EXISTS getBatchCode;
DELIMITER //
CREATE PROCEDURE getBatchCode(mfgDate DATE, OUT code CHAR(8))
BEGIN
  DECLARE num CHAR(2);
  DECLARE dateCode CHAR(6) DEFAULT DATE_FORMAT(mfgDate, '%y%m%d');

  SELECT SUBSTRING(batchcode, 7) FROM batch WHERE date_manufactured=mfgDate ORDER BY batchcode DESC LIMIT 1
   INTO num;
  SET num = num + 1;

  IF num < 10 THEN
    SET num = CONCAT('0', num);
  END IF;

  SET code = CONCAT(dateCode, num);
END;
//
DELIMITER ;