How to execute multiple SELECT in one stored procedure?

Question

MySQL

I am writing something that does something, but I understand that it is still only performed once.
inCategoryId - procedure input variable

BEGIN
  DECLARE cpi  INT;
  SET cpi = inCategoryId;
    

    WHILE cpi > 0 DO
    	SELECT category_id
        FROM category 
        WHERE category_id = cpi;
        
        SET cpi = cpi - 1;
    END WHILE;
END

In general, if we simplify, then we need the following - there is an input digit (category number), it can be a descendant of any category, which can also be someone else's descendant, and so on ad infinitum.

I want to choose from all these categories, for example, a pair of products - while the nesting level is unknown, maybe the root one.

Answer 1

nested set are very convenient for this purpose. and without nested procedures - everything is selected with one simple request.

Answer 2

Here is an example of a function to find the id of the root category

CREATE DEFINER=`root`@`localhost` FUNCTION `FIND_CATEGORY_ROOT_ID`(category INT) RETURNS int(11)
    DETERMINISTIC
BEGIN
  DECLARE root INT;
  WHILE category IS NOT NULL DO
    SET root = category;
    SELECT parent_id INTO category FROM tree WHERE id = category;
  END WHILE;
  RETURN root;
END