Recursive Stored Functions in MySQL

Recursive stored functions in MySQL

MySQL does not allow recursive FUNCTIONs, even if you set max_sp_recursion_depth.

It does allow up to 255 recursion in a PROCEDURE if you set max_sp_recursion_depth.

So I recommend that you replace your function with a procedure, using an INOUT variable for the return_path.

MySQL does not support recursive functions? why? since when?

No problem, Jenco.
Not so efficient as PostgreSQL functions, but it's possible in MySQL procedures also:

DELIMITER $$
DROP PROCEDURE IF EXISTS test.factorial_proc$$
CREATE PROCEDURE test.factorial_proc
(
   IN n   BIGINT, 
  OUT res BIGINT 
) 
BEGIN
  SET max_sp_recursion_depth=10; 
  IF n >= 2 THEN
    CALL test.factorial_proc (n-1, res);
    SELECT n * res INTO res;
  ELSE
    SELECT n INTO res;
  END IF;
END$$
DELIMITER ;

[test]> CALL test.factorial_proc (5, @res);
[test]> CALL test.factorial_proc (5, @res1);
[test]> select @res * @res1;
+--------------+
| @res * @res1 |
+--------------+
|        14400 |
+--------------+

Sergei Zaytsev.

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;

Well, I have a problem about recursion in MySql, about creating a recursive factorial function. it gives me error 1424:

MySQL functions are not allowed to be recursive. Recursive stored functions in MySQL

Stored procedure with recursive call using mysql

The answer of @Shadow is correct. Here a practical example that can help you:

DELIMITER //

DROP TABLE IF EXISTS `employee`//
DROP PROCEDURE IF EXISTS `getVolume`//

CREATE TABLE `employee` (
  `emp_name` VARCHAR(255),
  `val` VARCHAR(255)
)//

INSERT INTO `employee` (`emp_name`, `val`)
VALUES
('demo', 'new'),
('new', 'd.new'),
('d.new', 'view'),
('view', 'hello'),
('hello', NULL)
//

CREATE PROCEDURE `getVolume`(
  IN `param_name` VARCHAR(255),
  OUT `result` VARCHAR(255)
)
BEGIN
  DECLARE `next_param_name` VARCHAR(255);
  SELECT `val` INTO `next_param_name`
  FROM `employee`
  WHERE `emp_name` = `param_name`;

  IF `next_param_name` IS NULL THEN
    SET `result` := `param_name`;
  ELSE
    -- CALL `getVolume`(`result`, '');
    CALL `getVolume`(`next_param_name`, `result`);
    -- SET `result` := CONCAT(`param_name`, ',', IFNULL(`result`, ''));
  END IF;
END//

DELIMITER ;

SQL Fiddle demo

MySQL query or stored procedure that calls itself recursively and returns all nodes for selected parent

a similar question was answered here:
https://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161#7161

You need to use stored procedures for this.

---

Related Topics