The Following procedure after getting called, shows up error “The Result return more than one row” where the input value given is just one parameter.
x
DELIMITER $$
CREATE PROCEDURE p( IN en int,OUT ename int, OUT incr_sal int, OUT s_sal
int, OUT count1 int)
BEGIN
DECLARE sal INT DEFAULT 0;
DECLARE cnt int DEFAULT 0;
DECLARE emp_name int DEFAULT 0;
SELECT `ename`,`salary` INTO sal, emp_name
FROM works w
WHERE `ename`=en;
SELECT COUNT(m.empname) INTO cnt
FROM manages m
WHERE m.empname=en
GROUP BY m.empname;
SET @ename=emp_name; SET @incr_sal=sal;
IF sal>50000 && cnt>2 THEN SET sal=sal+((sal*5)/100);
ELSEIF sal>50000 && sal<60000 && cnt>=1 THEN SET sal=sal+((sal*2)/100);
ELSEIF sal>10000 && cnt=0 THEN SET sal=sal+((sal*1)/100);
end IF;
SET @s_sal=sal;
SET @count1=cnt;
SELECT @ename,@incr_sal,@s_sal,@count1;
END $$
DELIMITER ;
CALL p('Sam',@ename,@incr_sal,@s_sal,@cnt);
Advertisement
Answer
Execute query seperately and see the result
SELECT `ename`,`salary` INTO sal, emp_name
FROM works w
WHERE `ename`=en;
Are you expected more than one row here or just one. If expecting one then just add limit 1 to query as :
SELECT `ename`,`salary` INTO sal, emp_name
FROM works w
WHERE `ename`=en limit 1;