CODEWITHSUNDEEP
mysql
Sam
Sam

Reputation: 141

Error Number: 1172 Result consisted of more than one row

I am trying to make a stored procedure to apply leaves for employees. My stored procedure is as follows 

DELIMITER $$;
CREATE PROCEDURE start_crediting(IN elc_date date)
BEGIN
DECLARE cur_date DATE DEFAULT NULL;

SELECT set_credit_values(emp_id,lpc_id,elc_date) INTO cur_date  
    FROM erp_leave_policy,erp_employees,erp_clients,erp_employee_leave_policy 
    WHERE emp_status=1 AND cli_status=1 AND lpc_status=1 AND 
    emp_id = elp_fk_employees AND lpc_id = elp_fk_leave_policy AND cli_id = emp_fk_clients;

END $$;
DELIMITER ;

When I call the procedure getting the following error

Error Number: 1172
Result consisted of more than one row
CALL start_crediting('2017-02-01')

I need to pass each row in the SELECT query to the function set_credit_values(). I had tested the SELECT query, it is working fine having two rows. when I pass each row individually using cursor, it is working ok. But in my live server there are hundreds of employees. So I think that it may take more time. The following query is working successfully and is equivalent to above query but here calling the function set_credit_values for each row using cursor. So it may slower than above query. I need a fast working solution.

DELIMITER $$;
            CREATE PROCEDURE start_crediting(IN elc_date date)
            BEGIN
                DECLARE v_finished INT(11) DEFAULT 0;
                DECLARE my_lpc_id INT(11) DEFAULT 0;
                DECLARE my_emp_id BIGINT(20) DEFAULT 0;


                DEClARE emp_cursor CURSOR FOR 

                -- Taking all active employees with their leave policy, those are having a leave policy assigned.
                SELECT lpc_id,emp_id  FROM erp_leave_policy,erp_employees,erp_clients,erp_employee_leave_policy 
                        WHERE emp_status=1 AND cli_status=1 AND lpc_status=1 AND 
                        emp_id = elp_fk_employees AND lpc_id = elp_fk_leave_policy AND cli_id = emp_fk_clients;

                DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

                OPEN emp_cursor;

                    get_emp: LOOP

                        FETCH emp_cursor INTO my_lpc_id,my_emp_id;

                        IF v_finished = 1 THEN 
                        LEAVE get_emp;
                        END IF;

                        -- calling function inseterLeave()
                        SELECT set_credit_values(my_emp_id,my_lpc_id,elc_date) FROM DUAL;


                    END LOOP get_emp;

                CLOSE emp_cursor;

                END $$;
                DELIMITER ;

Upvotes: 0

Views: 647

Answers (1)

Devart
Devart

Reputation: 121952

The SELECT query returns more then one row, so it is something like array. Then you are trying to pass this array-value to scalar variable cur_date.

There are no array-like types in MySQL. As a workaround you can use temporary table to store those array-values. Use INSERT..SELECT statement to fill the table with result dataset.

Upvotes: 1

Related Questions