What are recursive stored procedures and why MySQL limits the recursion?

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.