Knowledge Walls
John Peter
Pune, Maharashtra, India
How to find factorial with mysql recursive stored procedure with example
17184 Views
How to create recursion in Mysql Procedures? 
Mysql version should be >= 5.
 
Have to set system parameters. This means putting the recursion count limit.
SET @@GLOBAL.max_sp_recursion_depth = 255;
SET @@session.max_sp_recursion_depth = 255;
 
Change thread_stack size in in.cnf file
thread_stack = 64M
or
thread_stack = 128M
Example Factorial Program with Recursive Algorithm
DROP PROCEDURE IF EXISTS find_fact;
DROP PROCEDURE IF EXISTS factorial;

DELIMITER $$
CREATE PROCEDURE find_fact(IN n INT)
BEGIN
    SET @@GLOBAL.max_sp_recursion_depth = 255;
    SET @@session.max_sp_recursion_depth = 255;
 
    CALL factorial(n,@fact);
    
    SELECT @fact;
END
$$

DELIMITER $$
CREATE PROCEDURE factorial(IN n INT,OUT fact INT)
BEGIN
    IF n = 1 THEN
        SET fact := 1;
    ELSE
        CALL factorial(n-1,fact);
        SET fact := n * fact;
    END IF;
END
$$
Calling Procedure & Output 
CALL find_fact(5);

Next Topics
Next lessons of current book.
INFORMATION_SCHEMA Db of MySQL
INFORMATION_SCHEMA Db of MySQL
INFORMATION_SCHEMA Db of MySQL
  Copyright © 2014 Knowledge walls, All rights reserved
KnowledgeWalls
keep your tutorials and learnings with KnowledgeWalls. Don't lose your learnings hereafter. Save and revise it whenever required.
Click here for more details