drop procedure sp_analyte_migration ;


Create PROCEDURE sp_analyte_migration (as_catg varchar(1))
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE v_testid INT;
	DECLARE v_analyteid VARCHAR(50);
	DECLARE v_count INT Default 0 ;

	DECLARE cur_lab_analyte CURSOR FOR 
	SELECT id, analyteid FROM lab_analyte_master
	where analyte_category = as_catg;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
	OPEN cur_lab_analyte;  

	read_loop: LOOP
		FETCH cur_lab_analyte INTO v_testid, v_analyteid;
		IF done THEN
			LEAVE read_loop;
		END IF;

			Select count(*)
			INTO	v_count
			FROM	lab_analyte_category_map
			WHERE	parent_lab_analyte_id = v_testid;

			IF v_count = 0 THEN
				CALL sp_analyte_grp_migration_indv(v_testid, v_analyteid);
			END IF;

	END LOOP read_loop;

	CLOSE cur_lab_analyte; 
END;
