DROP PROCEDURE IF EXISTS `procedure_foo`; DELIMITER $$ CREATE PROCEDURE `procedure_foo`() BEGIN DECLARE done INTEGER DEFAULT FALSE; DECLARE token_id, group_id, group_domain_id, type_id INTEGER; DECLARE group_name VARCHAR(64); DECLARE group_code VARCHAR(255); DECLARE cur CURSOR FOR SELECT `id`, `name`, `domain_id` FROM `civicrm_group`; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SELECT id INTO type_id FROM `acme_token_type` WHERE `name` = 'in_group' LIMIT 1; OPEN cur; read_loop: LOOP FETCH cur INTO group_id, group_name, group_domain_id; IF done THEN LEAVE read_loop; END IF; -- Double Insert Protection SELECT COUNT(id) INTO token_id FROM `acme_token_entry` WHERE `token` = CONCAT('in_group_', group_id) LIMIT 1; IF token_id > 0 THEN ITERATE read_loop; END IF; -- Get Group Code SELECT bb.code INTO group_code FROM `bb_object_type` bb JOIN `civicrm_group` gr ON (bb.id = gr.group_type) WHERE gr.id = group_id; -- Insert based on group type: IF group_code = 'FRIENDS' THEN INSERT INTO `acme_token_entry` (`type_id`, `domain_id`, `is_system`, `token`, `label`, `access_level`) VALUES (type_id, group_domain_id, 1, CONCAT('in_group_', group_id), CONCAT('In Group ', group_name), 99); ELSEIF group_code = 'OPT_OUT' THEN INSERT INTO `acme_token_entry` (`type_id`, `domain_id`, `is_system`, `token`, `label`, `access_level`) VALUES (type_id, group_domain_id, 1, CONCAT('in_group_', group_id), CONCAT('In Group ', group_name), 98); ELSEIF group_name = 'All Constituents' THEN INSERT INTO `acme_token_entry` (`type_id`, `domain_id`, `is_system`, `token`, `label`, `access_level`) VALUES (type_id, group_domain_id, 1, CONCAT('in_group_', group_id), CONCAT('In Group ', group_name), 99); ELSE INSERT INTO `acme_token_entry` (`type_id`, `domain_id`, `is_system`, `token`, `label`, `access_level`) VALUES (type_id, group_domain_id, 0, CONCAT('in_group_', group_id), CONCAT('In Group ', group_name), 0); END IF; END LOOP; CLOSE cur; END $$ delimiter ; CALL procedure_foo(); DROP PROCEDURE procedure_foo;