MYSQL Procedure not getting called

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;

Advertisement

Answer