October 21, 2023
0 comment-blank

MySql Call Self SP known as a recursive stored procedure

Sample SP Code

DELIMITER //
CREATE PROCEDURE RecursiveSP(IN n INT)
BEGIN
  -- Base case: when n is less than or equal to 0, stop the recursion
  IF n <= 0 THEN
    SELECT 'Reached the base case';
  ELSE
    -- Recursive case: call the same procedure with a decreased value of n
    CALL RecursiveSP(n - 1);
    SELECT CONCAT('Recursive call with n = ', n);
  END IF;
END;
//
DELIMITER ;

There also need to configure MySql setting to call same SP. Run this code will solve the issue.

SET max_sp_recursion_depth = your_desired_value;

If not solve then need to Configure MySql config file. Follow this step by step.


If you’re using a VPS with Ubuntu and the suggested solution for increasing the max_sp_recursion_depth variable is not working, you might need to adjust the system variables in your MySQL configuration file. Here’s how you can do that:

  1. Locate your MySQL configuration file. In Ubuntu, the MySQL configuration file is usually located at /etc/mysql/mysql.conf.d/mysqld.cnf. You may need root or superuser permissions to edit this file.
  2. Open the configuration file using a text editor. You can use nano or vim:bashCopy codesudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
  3. Find the section for MySQL server configuration. It might look like this:csharpCopy code[mysqld]
  4. Add or modify the max_sp_recursion_depth variable in this section:csharpCopy code[mysqld] max_sp_recursion_depth = your_desired_value Replace your_desired_value with the maximum recursion depth you want to set. For example:csharpCopy code[mysqld] max_sp_recursion_depth = 100
  5. Save the configuration file and exit the text editor.
  6. Restart the MySQL service to apply the changes:bashCopy codesudo service mysql restart

Please make sure you have a valid use case for increasing the recursion depth and be cautious when modifying MySQL configuration files. Setting very high values for max_sp_recursion_depth can lead to high resource usage and possible stack overflow errors, so use it judiciously based on your specific requirements.

After following these steps, the max_sp_recursion_depth should be increased in your MySQL configuration, and your stored procedures should be able to use a deeper recursion depth.