MySQL cursor not working with Stored Procedure

Question

I wrote store procedure in mysql. Step were followed this website http://www.mysqltutorial.org/mysql-cursor/ But it doesn't work. Here is code

---

DELIMITER $$

USE `hr`$$

DROP PROCEDURE IF EXISTS `at_getShift`$$

CREATE DEFINER=`root`@`%` PROCEDURE `at_getShift`()

BEGIN

    DECLARE finished BOOLEAN DEFAULT FALSE;
    DECLARE employeeID VARCHAR(255);-- Default "";

    -- declare cursor for employee email
    DECLARE hrEmployee CURSOR FOR SELECT EmployeeID FROM h_employees WHERE EmployeeID IN ('100013', '100014');

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;

    DROP TABLE IF EXISTS temp;
    CREATE TABLE IF NOT EXISTS temp(
      `Code` VARCHAR(255)
    );  

    OPEN hrEmployee;

    get_employee: LOOP
    FETCH hrEmployee INTO employeeID;
    INSERT INTO temp(`Code`) VALUE (employeeID);
    -- If no any row, leave loop
    IF finished THEN 
        INSERT INTO temp(`Code`) VALUE ("112");
        CLOSE hrEmployee;
        LEAVE get_employee;
    END IF;

    -- insert temp
    INSERT INTO temp(`Code`) VALUE ("111");
    END LOOP get_employee;

    SELECT * FROM temp;
END$$

DELIMITER ;

---

Execute: CALL at_getShift();

Result is:

2 rows in temp table ( 1 null, 1 is 112)

Please kindly help me to resolve this trouble.

Answer 1

In a SQL statement in MySQL stored program, the references to procedure variables take precedence over references to columns.

That is, when an identifier in a SQL statement matches a procedure variable, the SQL statement references the procedure variable.

References that are qualified with the table name or table alias reference columns from the table, even when there is a procedure variable with the same name.

Demonstration:

 CREATE TABLE emp (id INT);
 INSERT INTO emp (id) VALUES (101),(102);

 DELIMITER $$


 CREATE PROCEDURE foo()
 BEGIN
   DECLARE id INT DEFAULT 3;

   -- this query returns 3 for all rows in emp
   -- because "id" is a reference to the procedure variable
   SELECT id FROM emp WHERE id = 3;

   -- this query returns no rows
   -- because "id" is a reference to the procedure variable
   SELECT id FROM emp WHERE id = 101;

   -- this query references columns in the table because
   -- references to "id" are qualified
   SELECT t.id FROM emp t WHERE t.id = 101;

 END$$

 DELIMITER ;


 CALL foo;

The first query returns value of procedure variable for all rows from emp

    id
 -----
     3
     3

second query returns no rows

    id
 -----

third query returns references "id" column in table:

    id
 -----
   101 

---

The takeaway are two "best practices":

• qualify all column references in a SQL statement in a procedure

and

• procedure variable names should differ from names of columns, the usual pattern is to use a distinctive prefix on variables. As a trivial example: v_id, v_name, etc.

Both of these practices make it easier for a human reader to decipher a procedure.

Distinctive naming of procedure variables does reduce the chances of collisions, but does not invalidate the "best practice" of qualifying all column references in SQL statements. Both of those serve to make the author's intent more clear to the human reader.

---

EDIT:

I attempted to answer the question I thought you were asking... "Why is my procedure not doing what I expect it to?".

Beyond the answer to the question you asked... the operation that your procedure appears to be performing (populating a temporary table with a set of rows) that operation could be performed much faster and more efficiently by processing the rows as a set, rather than issuing painfully inefficient individual insert statements for each row. In terms of performance, a cursor loop processing RBAR (row-by-agonizing-row) is going to eat your lunch. And your lunch box.

DELIMITER $$

CREATE PROCEDURE `at_getShift_faster`()
BEGIN
   -- ignore warning message when dropping a table that does not exist
   DECLARE CONTINUE HANDLER FOR 1305 BEGIN END;
   DROP TABLE IF EXISTS temp;
   CREATE TABLE IF NOT EXISTS temp(`Code` VARCHAR(255));  

   INSERT INTO temp (`Code`)  
     SELECT h.EmployeeID 
       FROM h_employees h
      WHERE h.EmployeeID IN ('100013', '100014')
   ;

   SELECT * FROM temp;
END$$

DELIMITER ;