Stored Procedures calls two other stored procedures, but only 1 seems to run. No error thrown

Question

Problem:

Hi I've got two stored procedures, that I try to run from another one. - If I call them one after the other from phpmysqladmin, everything works fine. - If I call the stored procedure, that calls the other two, I don't get an error. So far so good. But the problem is, that the operations from the second stored procedure aren't executed.

Already tried to run it in just one sp...

I also tried to run both stored procedures (sp1,sp2) in one stored procedure, with the same effect.

Could that be the problem?

In the first sp I use a statement like this:

Select @var:= ....

Here is the code:

In the first stored procedure I generate a dynamic query and execute it.

Procedure 1

CREATE PROCEDURE `sp_prepare_valid_choices`(IN p_request_id Bigint)
BEGIN
  DECLARE num_rows INT DEFAULT 0;
  DECLARE no_more_rows BINARY;
  DECLARE no_more_subrows BINARY;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE var_choice_group BIGINT DEFAULT 0;

-- Declare Cursor for the loop through the constraint_groups
  DECLARE cur_constraint_group CURSOR FOR     
  SELECT distinct choice_constraint_group FROM casainte_choice_constraint
    WHERE choice_id_rule_parameter IN (SELECT choice_id FROM casainte_request_detail
                                        where request_id = p_request_id);
-- DECLARE 'handlers' for exceptions
  DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET no_more_rows := TRUE;

-- DELETE OLD VALUES
  DELETE FROM tmp_casainte_valid_choices
  WHERE request_id = p_request_id;
  DELETE FROM tmp_casainte_valid_choices_for_request
  WHERE request_id = p_request_id;

-- OPEN CURSOR AN PROCESS CONSTRAINT_GROUPS
  OPEN cur_constraint_group;
  SELECT FOUND_ROWS() INTO num_rows;

  choice_group_loop: LOOP
    FETCH cur_constraint_group
    INTO var_choice_group;

  IF no_more_rows THEN
    CLOSE cur_constraint_group;
    LEAVE choice_group_loop;
  END IF;
-- PAYLOAD
 -- INSERT THE VALID CHOCIES INTO tmp_casainte_valid_choices 
 SELECT @var_sql_query := CONCAT('INSERT INTO tmp_casainte_valid_choices ','SELECT ',p_request_id,' as request_id,  `casainte_choice_constraint`.`choice_constraint_id`,`casainte_choice_constraint`.`choice_constraint_group`
    ,AVG(IF (`casainte_request_detail`.`choice_varchar_value`', `casainte_choice_constraint`.`choice_constraint_operator`, '\'',`casainte_choice_constraint`.`choice_constraint_value`, '\'',',1,0 )) AS VALID
FROM `casainte_choice_constraint`
LEFT JOIN `casainte_request_detail` ON `casainte_request_detail`.`choice_id` = `casainte_choice_constraint`.`choice_id_rule_parameter`
WHERE `casainte_choice_constraint`.choice_constraint_group =' , var_choice_group,
' GROUP BY `casainte_choice_constraint`.choice_constraint_group')
FROM `casainte_choice_constraint` WHERE `casainte_choice_constraint`.choice_constraint_group = var_choice_group;
PREPARE SQL_STATEMENT FROM @var_sql_query;
EXECUTE SQL_STATEMENT;
-- INCREMENT THE COUNTER
SET loop_cntr = loop_cntr + 1;

END LOOP choice_group_loop;



END$$

Procedure 2

In the second stored procedure I insert the values into a table.

delimiter $$
CREATE PROCEDURE `sp_insert_valid_choices`(IN p_request_id Bigint)
BEGIN
INSERT INTO tmp_casainte_valid_choices_for_request
(request_id, choice_id)
SELECT DISTINCT p_request_id, choice_id FROM casainte_choice ac
-- RULE 1 ALL CHOICES WITHOUT CONSTRAINTS
WHERE ac.choice_id NOT IN (SELECT choice_id_rule_target FROM casainte_choice_constraint)
-- RULE 2 ALL CHOICES WITH CONSTRAINTS, THAT ARE NOT YET ANSWERED
OR ac.choice_id NOT IN (SELECT choice_id_rule_target FROM casainte_choice_constraint
WHERE choice_id_rule_parameter IN (SELECT choice_id FROM casainte_request_detail WHERE request_id = p_request_id))
-- RULE 3 ALL CHOICES WITH CONSTRAINTS, THAT ARE TRUE
OR ac.choice_id IN (SELECT choice_id_rule_target FROM casainte_choice_constraint
WHERE choice_constraint_group IN (SELECT choice_constraint_group FROM tmp_casainte_valid_choices WHERE request_id = p_request_id AND VALID = 1));
END$$

Procedure 3

The third stored procedure calls the 1st sp, then the 2nd sp.

delimiter $$
CREATE PROCEDURE `sp_generate_valid_choices`(IN p_request_id Bigint)
BEGIN
Call `sp_prepare_valid_choices`(p_request_id);
Call `sp_insert_valid_choices`(p_request_id);
END$$