title: Mysql Call function inside another function
body: <p>I have two functions. set_credit_values and do_credit. I will call the second one from inside first one. Then it is getting error <code>1 errors were found during analysis.Unexpected character. (near ":" at position 983)</code>, <code>#1415 - Not allowed to return a result set from a function</code>. My code is as follows</p>
  
  <pre><code>            DROP FUNCTION IF EXISTS do_credit;
  
              DELIMITER $$;
              CREATE FUNCTION do_credit (ltp_id int, cr_count tinyint, cr_on tinyint, next_cr date, cr_period tinyint) 
              RETURNS INT DETERMINISTIC
              BEGIN
  
                  INSERT IGNORE INTO aaa_test(aaa_ltp_id, aaa_cr_count, aaa_cr_on, aaa_next_cr, aaa_cr_period) VALUES 
                   (ltp_id, cr_count, cr_on, next_cr, cr_period);
                  RETURN LAST_INSERT_ID();
  
              END $$;
              DELIMITER ;   
  
              DROP FUNCTION IF EXISTS set_credit_values;
  
              DELIMITER $$;
              CREATE FUNCTION set_credit_values (emp_id bigint, lpc_id int, elp_date date) RETURNS boolean DETERMINISTIC
              BEGIN
  
                  DECLARE ltp_id INT(11) DEFAULT 0;
                  DECLARE cr_count tinyint(4) DEFAULT 0;
                  DECLARE cr_on tinyint(1) DEFAULT 0;
                  DECLARE next_cr DATE DEFAULT NULL;
                  DECLARE cr_period tinyint(4) DEFAULT 0;
  
                  DECLARE v_finished INT(11) DEFAULT 0;
  
                  DEClARE cur_set_credit CURSOR FOR                     
                  SELECT lpb_fk_leave_type, lpb_cr_count, lpb_cr_on, lpb_next_cr,  lpb_cr_period FROM erp_leave_policy_body
                  WHERE lpb_fk_leave_policy = lpc_id;
  
                  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
  
                  OPEN cur_set_credit;
  
                      get_cr: LOOP
  
                          FETCH cur_set_credit INTO ltp_id, cr_count, cr_on, next_cr, cr_period;
  
                          IF v_finished = 1 THEN 
                          LEAVE get_cr;
                          END IF;
  
                          SELECT do_credit(ltp_id, cr_count, cr_on, next_cr, cr_period) FROM DUAL;
  
  
                      END LOOP get_cr;
  
                  CLOSE cur_set_credit;
  
                  RETURN 1;
              END $$;
              DELIMITER ;
  </code></pre>
  
  <p>It is working me successfully if I write the insert query inside set_credit_values function without calling the do_credit function as follows</p>
  
  <pre><code>            DROP FUNCTION IF EXISTS set_credit_values;
  
              DELIMITER $$;
              CREATE FUNCTION set_credit_values (emp_id bigint, lpc_id int, elp_date date) RETURNS boolean DETERMINISTIC
              BEGIN
  
                  DECLARE ltp_id INT(11) DEFAULT 0;
                  DECLARE cr_count tinyint(4) DEFAULT 0;
                  DECLARE cr_on tinyint(1) DEFAULT 0;
                  DECLARE next_cr DATE DEFAULT NULL;
                  DECLARE cr_period tinyint(4) DEFAULT 0;
  
                  DECLARE v_finished INT(11) DEFAULT 0;
  
                  DEClARE cur_set_credit CURSOR FOR                     
                  SELECT lpb_fk_leave_type, lpb_cr_count, lpb_cr_on, lpb_next_cr,  lpb_cr_period FROM erp_leave_policy_body
                  WHERE lpb_fk_leave_policy = lpc_id;
  
                  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
  
                  OPEN cur_set_credit;
  
                      get_cr: LOOP
  
                          FETCH cur_set_credit INTO ltp_id, cr_count, cr_on, next_cr, cr_period;
  
                          IF v_finished = 1 THEN 
                          LEAVE get_cr;
                          END IF;
  
                          INSERT IGNORE INTO aaa_test(aaa_ltp_id, aaa_cr_count, aaa_cr_on, aaa_next_cr, aaa_cr_period) VALUES 
                   (ltp_id, cr_count, cr_on, next_cr, cr_period);
  
  
                      END LOOP get_cr;
  
                  CLOSE cur_set_credit;
  
                  RETURN 1;
              END $$;
              DELIMITER ;
  </code></pre>
  
  <p>But I need it to be do in do_credit function because I need to do some other queries with this. So please anybody help</p>