CODEWITHSUNDEEP
mysqlcursor
Sagar Sutar
Sagar Sutar

Reputation: 7

Pass a variable into CURSOR's SELECT Query (MySQL)

I'm trying the stored procedure below. However, when i pass the actual column name in 'DECLARE cur1' line, the SP returns correct value but when I pass variable name i.e. input parameter (colName), it returns 0. I've added comments in my code below. Is the code correct?

DROP PROCEDURE IF EXISTS test1.checkHardcodedField;
CREATE PROCEDURE test1.checkHardcodedField(IN textValue CHAR(10), colName CHAR(10), OUT counter VARCHAR(100))
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE temp CHAR(16);
  DECLARE i INT DEFAULT 0;

  -- Issue with this statement, returns incorrect value.
  DECLARE cur1 CURSOR FOR SELECT colName FROM data1;   

  -- This statement works. MSH3 is actual column name
  -- DECLARE cur1 CURSOR FOR SELECT MSH3 FROM data1; 

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO temp;

    IF done THEN
      LEAVE read_loop;
    END IF;
    IF temp = textValue THEN
      SET i = i + 1;
    END IF;
  END LOOP;

  SET counter = i;

  CLOSE cur1;
END;

Upvotes: 0

Views: 2059

Answers (1)

Akina
Akina

Reputation: 42632

CREATE PROCEDURE procedure_name (..., fieldname VARCHAR, ...)
BEGIN
    CREATE TEMPORARY TABLE tmp (field VARCHAR(10));
    SET @sql := CONCAT('INSERT INTO tmp SELECT ', fieldname, ' FROM tablename;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
    BEGIN
        DECLARE cur CURSOR FOR SELECT field FROM tmp;
        ...
    END;
    DROP TABLE tmp;
END

Upvotes: 1

Related Questions