Mysql | MariaDB create function, For Loop, Serial Number example

 DROP FUNCTION IF EXISTS max_nomor_anggota;

DELIMITER |

CREATE FUNCTION max_serial_number(param_1 VARCHAR(255), param_2 VARCHAR(255)) RETURNS VARCHAR(10)

BEGIN

DECLARE nomor_anggota_var VARCHAR(255);

DECLARE nomor VARCHAR(255);

DECLARE urut VARCHAR(255);

FOR i IN  1..9999

DO

SET urut = LPAD(i, 4, "0");

SET nomor = concat(param_1, param_2, urut);

IF (SELECT NOT EXISTS(SELECT * from table_name WHERE nomor_anggota = nomor AND id_wilayah=CAST(param_1 as INTEGER) AND id_daerah=CAST(param_2 as INTEGER) ORDER BY nomor_anggota)) THEN 

RETURN nomor;

END IF;

END FOR;

RETURN nomor;

END 

|

DELIMITER ;