mysql 存储过程递归,使用mysql递归调用的存储过程

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;

解决方案

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 ;