Geek asks and answers

How to get depth in recursion of mysql storage procedure?

I have mysql recursive stored procedures for which I set max_sp_recursion_depth=10.

Now, without setting a local variable, I would like to know what level of recursion falls on a single execution.

I think, of course, there is a session variable that stores depth (as you know, when you reach the maximum level), but I could not find it. I would not use a variable to do this gradually. How can I find out this (if any) system variable?

+3
source share
1 answer

I know that you specifically asked how to do this without a user-created variable - but for others who come across this thought, it would be wise to post how to do this with one, as it is quite simple:

CREATE PROCEDURE sp_recursive
BEGIN
  // ... DECLAREs here

  -- Set maximum recursion depth (max is 255)
  SET @@SESSION.max_sp_recursion_depth = 10;

  -- Increment current recursion depth
  SET @recursion_depth = IFNULL(@recursion_depth + 1, 0);

  -- ... More stored procedure code

  -- Decrement current recursion depth. Note: Care must be taken to ensure this line
  -- is *always* executed at the end of the stored procedure.
  SET @recursion_depth = @recursion_depth - 1;
END

Explanation

An @recursion_depthextended session variable is incremented by following the above instructions SETwhen entering a stored procedure. At the first input, the variable is not initialized and therefore it matters NULL- it is checked for IFNULL(), which reassigns it in this case. At the end of the stored procedure just before the exit, the depth should be reduced.

Additional notes

It is worth noting that SQL Server provides a built-in variable @@NESTLEVELto accomplish the above, but unfortunately MySQL does not seem to have an equivalent.

0

More articles:


All Articles