MYSQL Procedure not getting called

Tags: , ,



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.

    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);

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;  


Source: stackoverflow