Mysql – What’s wrong with the stored proc

MySQL

I have written a basic stored procedure, which looks up the contact from one table, links with a membership, and inserts the membership plus an original field (from one table) into the new one.

Unfortunately the entity_id insert is always the same. 13226 for all of the records. I've gone through the logic and query step by step. It would seem somewhere the entity_id value isn't being updated, so the same value gets inserted.

DROP PROCEDURE IF EXISTS data_membership_insert;
TRUNCATE test_table;

  DELIMITER ;;

  CREATE PROCEDURE data_membership_insert()
  BEGIN

    -- Declare variables
    -- Must be first, else will fail
    DECLARE DONE INTEGER DEFAULT 0;
    DECLARE CONTACT_ID INT DEFAULT 0;
    DECLARE MEMBERSHIP_ID INT DEFAULT 0;
    DECLARE PRIMARY_KEY INT DEFAULT 0;
    DECLARE NOMINATOR_CONTACT_ID INT DEFAULT 0;
    DECLARE COUNT INT DEFAULT 0;


    DECLARE cursor_i CURSOR FOR (
      SELECT id FROM civicrm_value_membership_3
      WHERE nominated_by_4 IS NOT NULL
      AND entity_id IS NOT NULL
      GROUP BY entity_id
      ORDER BY id
    );

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;

    SET CONTACT_ID = null;
    SET NOMINATOR_CONTACT_ID = null;
    SET MEMBERSHIP_ID = null;
    SET PRIMARY_KEY = null;
    SET COUNT = null;

    OPEN cursor_i;
      read_loop: LOOP

        FETCH cursor_i INTO PRIMARY_KEY;

      SET CONTACT_ID = null;
      SET NOMINATOR_CONTACT_ID = null;
      SET MEMBERSHIP_ID = null;
      SET COUNT = null;


      IF DONE = 1 THEN 
        LEAVE read_loop;
      END IF;


      SELECT entity_id
      FROM civicrm_value_membership_3
      WHERE id = PRIMARY_KEY
      INTO CONTACT_ID;


      SELECT id
      FROM civicrm_membership
      WHERE contact_id = CONTACT_ID
      LIMIT 1
      INTO MEMBERSHIP_ID;


      SELECT nominated_by_4
      FROM civicrm_value_membership_3
      WHERE id = PRIMARY_KEY
      LIMIT 1
      INTO NOMINATOR_CONTACT_ID;


      INSERT INTO test_table ( entity_id, nominated_by_48 )
      VALUES( MEMBERSHIP_ID, NOMINATOR_CONTACT_ID );


      END LOOP read_loop;
    CLOSE cursor_i;
    END;
    ;;

DELIMITER ;


CALL data_membership_insert();
DROP PROCEDURE IF EXISTS data_membership_insert;

Best Answer

Your insert statement:

INSERT INTO test_table ( entity_id, nominated_by_48 )
      VALUES( MEMBERSHIP_ID, NOMINATOR_CONTACT_ID );

It's inserting MEMBERSHIP_ID into entity_id and looking at how MEMBERSHIP_ID is fetched, problem seems to be there. If you have multiple records but limit 1 is used, it will most likely (results can be indeterminate though) pick the same value.

Possible solution is to change the query for cursor_i and select entity_id along with the ID and just use that value for insert when you loop through (no need to fetch entity_id again within the loop.

Related Question