I am trying to create a series of procedures to copy data from one enterprise to another.

I have a procedure with a a few cursors/loops to do the heavy lifting. The reason that I am not fetching all values in to variable and instead doing a second select is that some of the tables I am going against are extremely wide. Regardless, it is still returning records it should not.

I should return one record and get a single result in my audit table, but I get 3.

Even adding AND communityID = @communityID AND enterpriseID = @orgEnterpriseID to the insert/select condition gives the same three results.

Audit results:

Code:
originalID   newID  typeID  communityID  
----------  ------  ------  -------------
       733    1498      16             63
      1085    1496      16             63
      1225    1497      16             63
Corresponding rows in the data table on a regular query. While the enterpriseID is correctly matched, the communityID is different.

Code:
documentCategoryID  parentID  communityID  enterpriseID  userID  insertByUserID            insertDTS  name       
------------------  --------  -----------  ------------  ------  --------------  -------------------  -----------
               733        21           63             9   21280           21280  2010-05-19 01:28:03  one 
              1085         9           78             9   27524           27524  2011-11-09 17:59:59  two   
              1225        21           29             9   22890           22890  2012-07-23 16:23:21  three
Code:
DELIMITER $$

USE `dbname`$$

DROP PROCEDURE IF EXISTS `sp_copyPreMappingTables`$$

CREATE DEFINER=`jjones`@`%` PROCEDURE `sp_copyPreMappingTables`(IN communityID INT, IN orgEnterpriseID INT, IN newEnterpriseID INT, IN newClinicID INT)
BEGIN
    
    SET @communityID := communityID;
    SET @orgEnterpriseID := orgEnterpriseID;
    SET @newEnterpriseID := newEnterpriseID;
    SET @newClinicID := newClinicID;
    
		-- Document Category cursor
		BEGIN
			DECLARE v_finished16 INTEGER DEFAULT 0 ;
			DECLARE orgDocCatID INT;
			DECLARE document_category_cursor CURSOR FOR
			SELECT
				documentCategoryID
			FROM 
				db.documentCategory
			WHERE
				communityID = @communityID AND enterpriseID = @orgEnterpriseID;			
		
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished16 = 1;
				
			OPEN document_category_cursor;
			
			GET_DOCCAT: LOOP
				FETCH document_category_cursor INTO orgDocCatID;
				
				IF v_finished16 = 1 THEN
					CLOSE document_category_cursor;
					LEAVE GET_DOCCAT;
				END IF;
				
				SET @orgDocCatID := orgDocCatID;
				
				INSERT INTO db.documentCategory (
					parentID, communityID, enterpriseID, userID, insertByUserID, insertDTS, `name`
				) 
				SELECT 
					parentID, @communityID, @newEnterpriseID, userID, insertByUserID, insertDTS, `name` 
				FROM
					db.documentCategory 
				WHERE documentCategoryID = @orgDocCatID;
				
				SET @documentCategoryID := LAST_INSERT_ID();
				
				-- Audit and mapping reference
				INSERT INTO db.copyEnterpriseMapping VALUES(@orgDocCatID,@documentCategoryID,16,@communityID);
				
			END LOOP GET_DOCCAT;
		END;

                ...

    END$$

DELIMITER ;
Thank you.

Jeff