What are Recursive Stored Procedures and MySQL Recursion Limits

A stored procedure is called recursive if it calls itself. Basically, this concept is called recursion. MySQL limits the recursion so the errors will be less rigorous. We can check this limit with the help of the following query −

mysql> Show variables LIKE '%recur%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_sp_recursion_depth |   0   |
+------------------------+-------+
1 row in set (0.01 sec)

We can change this value up to 255 with the help of the following query −

mysql> SET @@GLOBAL.max_sp_recursion_depth = 255//
Query OK, 0 rows affected (0.00 sec)

mysql> Show variables LIKE '%recur%'//
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_sp_recursion_depth | 255   |
+------------------------+-------+
1 row in set (0.01 sec)

The limit can be extended while writing the procedure also.