1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| CREATE FUNCTION `f_new_code_client`(
`p_groupe` TINYINT(2) UNSIGNED,
`p_cp` TINYINT(2) UNSIGNED
)
RETURNS VARCHAR(7) CHARACTER SET utf8
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE v_id INTEGER UNSIGNED DEFAULT 0;
SELECT MAX(`t_code_client`.`id`) INTO v_id
FROM `t_code_client`
WHERE `t_code_client`.`id_groupe` = p_groupe
AND `t_code_client`.`cp` = p_cp;
SET v_id = IFNULL(v_id, 0) + 1;
IF (v_id > 999) THEN
RETURN -1;
ELSE
INSERT INTO `t_code_client`(id_groupe, cp, id)
VALUES (p_groupe, p_cp, v_id);
RETURN CONCAT(LPAD(p_groupe, 2, '0'), LPAD(p_cp, 2, '0'), LPAD(v_id, 3, '0'));
END IF;
END; |
Partager