mysql stored procedure that calls itself recursively

by

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;

More Related Contents:

  1. Can I create view with parameter in MySQL?
  2. Using MySQL query to traverse rows to make a recursive tree
  3. Connect By Prior Equivalent for MySQL
  4. How to raise an error within a MySQL function
  5. MySQL stored procedure vs function, which would I use when?
  6. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’
  7. Looping Over Result Sets in MySQL
  8. mysql dynamic query in stored procedure
  9. MySQL : transaction within a stored procedure
  10. Dynamic table names in stored procedure function
  11. List of Stored Procedures/Functions Mysql Command Line
  12. passing LIMIT as parameters to MySQL sproc
  13. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  14. How do you debug MySQL stored procedures?
  15. How can I use transactions in my MySQL stored procedure?
  16. MySQL – Recursing a tree structure
  17. MySQL error #2014 – Commands out of sync; you can’t run this command now
  18. MySQL Stored Procedures not working with SELECT (basic question)
  19. use a variable for table name in mysql sproc
  20. Split a string and loop through values in MySQL stored procedure
  21. How to schedule a stored procedure in MySQL
  22. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  23. MySQL : When stored procedure parameter name is the same as table column name
  24. get a recursive parent list
  25. How do I write an SP in phpMyAdmin (MySQL)?
  26. Call a stored procedure for each row returned by a query in MySQL
  27. Creating MYSQL Procedure in Laravel 4 Migrations
  28. is it possible to call a sql script from a stored procedure in another sql script?
  29. Mysql stored procedure don’t take table name as parameter
  30. How to pass a variable to a IN clause?

Leave a Comment