CODEWITHSUNDEEP
mysqlstored-procedures
Fred Prat
Fred Prat

Reputation: 11

MySql Stored Procedure var null

I'm quite new to mySql and in my stored procedure I can't get my variable id_lead to fetch into the cursor. In other words, inside the loop I do FETCH cursor_id_leads INTO id_lead; but its value is null everytime it iterates.

This procedure is supposed to change two rows in two different tables or rollback in case an error arises

DELIMITER $$
DROP PROCEDURE IF EXISTS modify_entity$$
CREATE DEFINER=`admin_base`@`%` PROCEDURE `modify_entity`(
    IN newEntity VARCHAR(100),
    IN currentEntity VARCHAR(100)
)
BEGIN
    DECLARE errno INT; 
    DECLARE errname VARCHAR(200); 
    DECLARE hasError INTEGER DEFAULT 0; 
    DECLARE numberOfEntitiesAffected INTEGER; 
    DECLARE numberOfLeadsAffected INTEGER; 
    DECLARE id_lead INTEGER; 
    DECLARE var_final_cursor INTEGER DEFAULT 0;
    DECLARE cursor_id_leads CURSOR FOR SELECT Id_lead FROM BASE_LEADS WHERE entity = currentEntity;
    DECLARE exit handler for SQLEXCEPTION 
    BEGIN
      SET huboerr = 1;
      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
      @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
      SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
      SELECT @full_error, hasError, numberOfEntitiesAffected, numberOfLeadsAffected;
      ROLLBACK;
    END;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_final_cursor := 1;
    
    SET numberOfEntitiesAffected := (SELECT COUNT(*) FROM BASE_ENTITIES WHERE entity = currentEntity);
    SET numberOfLeadsAffected := (SELECT COUNT(*) FROM BASE_LEADS WHERE entity = currentEntity); 

    START TRANSACTION;
    OPEN cursor_id_leads;
    bucle: LOOP
      FETCH cursor_id_leads INTO id_lead;
      select id_lead;  -- I use this line to figure out what values does id_lead take, but it's always null
      IF var_final_cursor = 1 THEN
        LEAVE bucle;
      END IF;
      UPDATE BASE_ENTITIES SET Entidad = newEntity WHERE Entidad = entidadActual;
      UPDATE BASE_LEADS SET Entidad = newEntity WHERE Id_lead = id_lead;
    END LOOP bucle;
    CLOSE cursor_id_leads;
    COMMIT;
    select errno, errname, hasError, numberOfEntitiesAffected, numberOfLeadsAffected, id_lead;
END$$
DELIMITER ;

The SELECT the cursor does should have data as I try it outside the procedure with the same values and it returns rows

Upvotes: 0

Views: 63

Answers (1)

Fred Prat
Fred Prat

Reputation: 11

I found the reason why it wasn't fetching data into id_lead. It was because the column in the table has the same name. I renamed my variable id_lead to id_lead_aux and it worked.

Upvotes: 1

Related Questions