Sam Sam -4 years ago 127
MySQL Question

getting errors when calling functions inside stored procedure

I am doing a stored to procedure to manage leaves of employees in a company. Leave should be credited to each employee periodically. My code is as follows

DROP FUNCTION IF EXISTS inserter;

DELIMITER $$;
CREATE FUNCTION inserter(emp_id bigint, lpc_id int) RETURNS boolean DETERMINISTIC
BEGIN

INSERT INTO aaa_test (aaa_emp_id,aaa_lpc_id) VALUES (emp_id,lpc_id);

RETURN 1;
END $$;
DELIMITER ;


DROP PROCEDURE IF EXISTS start_credit_test;

DELIMITER $$;
CREATE PROCEDURE start_credit_test()
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

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;

call inserter(my_emp_id,my_lpc_id);

END LOOP get_emp;

CLOSE emp_cursor;

END $$;
DELIMITER ;


I am using codeigniter. Then when I do
$this->db->querry("CALL start_credit_test();");
getting errors as
PROCEDURE abn_erp.inserter does not exist

The querry inside the procedure

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;


gives the output as
Click Here

Then I inserted function again as

DELIMITER $$;
CREATE FUNCTION inserter(emp_id bigint, lpc_id int) RETURNS boolean DETERMINISTIC
BEGIN

INSERT INTO aaa_test (aaa_emp_id,aaa_lpc_id) VALUES (emp_id,lpc_id);

RETURN 1;
END $$;
DELIMITER ;


But it shows the error
#1304 - FUNCTION inserter already exists

Answer Source

Please rename it with inseterLeave as shown below.

DELIMITER $$;
CREATE FUNCTION inseterLeave (emp_id bigint, lpc_id int) RETURNS boolean DETERMINISTIC
BEGIN
    INSERT INTO aaa_test (aaa_emp_id,aaa_lpc_id) VALUES (emp_id,lpc_id);   
    RETURN 1;
END $$;
DELIMITER ;

and rewrite procedure with below code.

You did not need call keyword to call function from procedure.I had made changes according to it.

       DROP PROCEDURE IF EXISTS start_credit_test;

            DELIMITER $$;
            CREATE PROCEDURE start_credit_test()
                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 

                    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;
 SELECT inseterLeave(my_emp_id,my_lpc_id) FROM DUAL; <-- Please remove call keyword from this line.Function did not require call keyword!>


                        END LOOP get_emp;

                    CLOSE emp_cursor;

                    END $$;
                    DELIMITER ; 

I think inseter is inbuilt functionor Function with same name already exist.Hope this will helps you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download