Question 72326 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    Check the MySQL’s max_sp_recursion_depth setting. As you are only looking for direct parents from the tree you can use a cursor loop instead of the recursion.

    The best solution is to update your old MySQL version to a newer one and you can use common table expressions for that.

    Login or Signup to reply.
  2. 0

    There are several things that make the procedure doesn’t work properly.

    1. max_sp_recursion_depth: if it assumes value of 0 this makes the procedure not recursive and stops the execution when you try to call the same procedure that is running.
    2. the select from marketing_profile_attribut is local and live only in the current istance! Each istance called in recursive way will produce only an entry. You need a structure in which you can store the results.

    Considering the two points abobe the procedure could be something like this:

    CREATE PROCEDURE `get_all_attributes`(IN profile_id INT)
BEGIN
    
  DECLARE parent_id INT DEFAULT NULL;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_table
    AS SELECT
    Id, attribut_rank, attribut_type, create_date, editor, label, memo, name, profile
    FROM 
    marketing_profile_attribut;
    
    SET @@max_sp_recursion_depth = 100;
        
  -- Get the attributes for the current profile
  INSERT INTO temp_table(Id, create_date)
  SELECT
    Id, attribut_rank, attribut_type, create_date, editor, label, memo, name, profile
  FROM 
    marketing_profile_attribut
  WHERE 
    profile = profile_id
  ORDER BY 
    Id;
        
  -- 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);
  else
      select * from temp_table;
      DROP TABLE temp_table;
    end if;
END
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search