/**
 * Yet more thoroughly excellt code from an ex-coworker...
 */
CREATE DEFINER=`root`@`localhost` FUNCTION `getnewprojectno`(paramdepartment VARCHAR(20)) RETURNS varchar(20) CHARSET latin1
    READS SQL DATA
    DETERMINISTIC
BEGIN
 
DECLARE pp VARCHAR(20);
DECLARE no INT;
DECLARE strno VARCHAR(20);
DECLARE ProjectNo VARCHAR(20);
 
SELECT projectprefix,nextprojectno INTO pp,no FROM systemparameters WHERE department = paramdepartment ;
 
IF (no < 10) THEN SET strno = CONCAT('00000',no);
ELSEIF (no < 100) THEN SET strno = CONCAT('0000',no);
ELSEIF (no < 1000) THEN SET  strno = CONCAT('000',no);
ELSEIF (no < 10000) THEN SET strno = CONCAT('00',no);
ELSEIF (no < 100000) THEN SET strno = CONCAT('0',no);
ELSE SET strno = CONCAT(no,'');
END IF;
 
SET  ProjectNo = CONCAT(pp,strno);
 
RETURN ProjectNo;
 
END;