Stored procedure with recursive call using mysql

Question

enter image description here get legside from binary

I have tried using stored procedure with recursive call. i need to display relevant name based on emp_name based on leg1

but it showing error like #1414 - OUT or INOUT argument 2 for routine sample.getVolume is not a variable or NEW pseudo-variable in BEFORE trigger

Here is my code,

DELIMITER $$
CREATE PROCEDURE getVolume( IN param_name VARCHAR(255), OUT result VARCHAR(255 ))
BEGIN 
SELECT val INTO result FROM employee WHERE emp_name = param_name ;
IF result IS NULL THEN
select result;
ELSE     
CALL getVolume(result, '');
END IF;
END $$
DELIMITER ;



SET @@GLOBAL.max_sp_recursion_depth = 255;
SET @@session.max_sp_recursion_depth = 255; 
call getVolume('new', @result);
select @result;

wchiquito · Accepted Answer · 2015-11-17 10:13:12Z

3

The answer of @Shadow is correct. Here a practical example that can help you:

DELIMITER //

DROP TABLE IF EXISTS `employee`//
DROP PROCEDURE IF EXISTS `getVolume`//

CREATE TABLE `employee` (
  `emp_name` VARCHAR(255),
  `val` VARCHAR(255)
)//

INSERT INTO `employee` (`emp_name`, `val`)
VALUES
('demo', 'new'),
('new', 'd.new'),
('d.new', 'view'),
('view', 'hello'),
('hello', NULL)
//

CREATE PROCEDURE `getVolume`(
  IN `param_name` VARCHAR(255),
  OUT `result` VARCHAR(255)
)
BEGIN
  DECLARE `next_param_name` VARCHAR(255);
  SELECT `val` INTO `next_param_name`
  FROM `employee`
  WHERE `emp_name` = `param_name`;

  IF `next_param_name` IS NULL THEN
    SET `result` := `param_name`;
  ELSE
    -- CALL `getVolume`(`result`, '');
    CALL `getVolume`(`next_param_name`, `result`);
    -- SET `result` := CONCAT(`param_name`, ',', IFNULL(`result`, ''));
  END IF;
END//

DELIMITER ;

SQL Fiddle demo

answered Nov 17, 2015 at 10:13

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nagoor meeran Over a year ago

Thanks wchiquito . Its working in minimum record. but if i used this SP in max no.of record(I have 4200 Records). It shows the error as "#1456 - Recursive limit 255 (as set by the max_sp_recursion_depth variable) was exceeded for routine getFinal ". Please see my updated question and guide to fix it.

wchiquito Over a year ago

@nagoormeeran: The maximum value of the variable max_sp_recursion_depth is: 255. See 5.1.4 Server System Variables (max_sp_recursion_depth).

Shadow · Accepted Answer · 2015-11-17 06:23:20Z

1

The problem is that when you call getResult() within getresult, you pass an empty string constant as a parameter:

CALL getVolume(result, '');

Declare a variable instead and pass that as a parameter. After the getVolume call do not forget to concatenate what is returned by getVolume to the result variable with a separator character. Are you sure that 255 characters will be enough to hold all values?

answered Nov 17, 2015 at 6:23

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Collectives™ on Stack Overflow

Stored procedure with recursive call using mysql

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related