how to write stored recursive procedure in order to get hierarchical element

Question

hello i have table profile that has many attributs, and the profile has on parent but the parent can have a parent etc.. I want to get all the list of the attributs(even the attributs of the parents) i wrote this stored procedure with mysql 5.1.42 but it returns only the attributs of the profile without including the attributs of the parents ...

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_all_attributes`(IN profile_id INT)
BEGIN
  DECLARE parent_id INT DEFAULT NULL;

  -- Get the attributes for the current profile
  SELECT
    Id, attribut_rank, attribut_type, create_date, editor, label, memo, name, profile
  FROM 
    marketing_profile_attribut
  WHERE 
    profile = profile_id
  ORDER BY 
    attribut_rank;

  -- Get the parent ID for the current profile
  SELECT 
    parent
  INTO
    parent_id
  FROM
    marketing_profile
  WHERE 
    Id = profile_id;

  -- If the parent ID is not null, call the stored procedure recursively with the parent ID
  IF parent_id IS NOT NULL THEN
    CALL get_all_attributes(parent_id);
  END IF;
END
...

please can you tell me what is wrong with this procedure i am working with mysql 5.1.42

Answer 1

Try this exactly as it is (it works on my db):

CREATE PROCEDURE `get_all_attributes2`(IN p_profile INT)
BEGIN
    DECLARE not_found INT DEFAULT FALSE;
  DECLARE parent_id INT DEFAULT NULL;
    
  DECLARE cur_data CURSOR FOR
  SELECT parent
  FROM marketing_profile
  WHERE Id = p_profile;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = TRUE;    
    
    SET @@max_sp_recursion_depth = 100;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_attributes (
    Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(45) DEFAULT NULL,
    memo VARCHAR(1000) DEFAULT NULL,
    label VARCHAR(45) DEFAULT NULL,
    editor INT(10) UNSIGNED DEFAULT NULL,
    attribut_type INT(10) UNSIGNED DEFAULT NULL,
    profile INT(10) UNSIGNED DEFAULT NULL,
    create_date DATETIME DEFAULT NULL,
    attribut_rank INT(10) UNSIGNED DEFAULT NULL,
    PRIMARY KEY (Id)
  );
    
    -- insert the attributes of p_profile into temporary table
    INSERT INTO temp_attributes
  SELECT Id, name, memo, label, editor, attribut_type, profile, create_date, attribut_rank
  FROM marketing_profile_attribut
  WHERE profile = p_profile;
    
    OPEN cur_data;
  data_loop: LOOP
    FETCH cur_data
        INTO parent_id;
    IF not_found THEN
      LEAVE data_loop;
    END IF;
        CALL get_all_attributes2(parent_id);
    END LOOP;
    CLOSE cur_data;
    
  select * from temp_attributes
    order by attribut_rank;
        
    DROP TABLE temp_attributes;
END