mysql stored procedure that calls itself recursively

its work only in mysql version >= 5

the stored procedure declaration is this,

you can give it little improve , but this working :

DELIMITER $$CREATE PROCEDURE calctotal(   IN number INT,   OUT total INT)BEGIN   DECLARE parent_ID INT DEFAULT NULL ;   DECLARE tmptotal INT DEFAULT 0;   DECLARE tmptotal2 INT DEFAULT 0;   SELECT parentid   FROM test   WHERE id = number INTO parent_ID;      SELECT quantity   FROM test   WHERE id = number INTO tmptotal;        IF parent_ID IS NULL    THEN    SET total = tmptotal;   ELSE         CALL calctotal(parent_ID, tmptotal2);    SET total = tmptotal2 * tmptotal;      END IF;END$$DELIMITER ;

the calling is like (its important to set this variable) :

SET @@GLOBAL.max_sp_recursion_depth = 255;SET @@session.max_sp_recursion_depth = 255; CALL calctotal(6, @total);SELECT @total;

Take a look at Managing Hierarchical Data in MySQL by Mike Hillyer.

It contains fully worked examples on dealing with hierarchical data.

How about avoiding procedures:

SELECT quantity from ( SELECT @rq:=parent_id as id, @val:=@val*quantity as quantity from (  select * from testTable order by -id limit 1000000 # 'limit' is required for MariaDB if we want to sort rows in subquery ) t # we have to inverse ids first in order to get this working... join ( select @rq:= 6 /* example query */, @val:= 1 /* we are going to multiply values */) tmp where id=@rq) c where id is null;

Check out Fiddle!

Note! this will not work if row's parent_id>id.

Cheers!