CODEWITHSUNDEEP
mysqlstored-procedurescursor
davidjhp
davidjhp

Reputation: 8116

MySQL stored procedure cursor not working

MySQL 5.5

CREATE TABLE `card` (
  `id` int,
  `cardnumber` varchar(100),
  `customer` varchar(100),
  PRIMARY KEY (`id`)
);

INSERT INTO `card` VALUES (1, '5000', 'Google');

 

DELIMITER // 
CREATE PROCEDURE `test` ()
BEGIN 

DECLARE finished INTEGER DEFAULT 0;
DECLARE cardnumber varchar(20) DEFAULT "";
DECLARE cursor1 CURSOR FOR SELECT cardnumber FROM card WHERE customer = 'Google';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

OPEN cursor1;
get_card: LOOP

  FETCH cursor1 INTO cardnumber;
  IF finished = 1 THEN 
    LEAVE get_card;
  END IF;

END LOOP get_card; 

SELECT cardnumber;

CLOSE cursor1;
END // 
DELIMITER ;

 

CALL test();

Returns no results, what did I do wrong?

Upvotes: 1

Views: 4140

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24022

There would be a ambiguity in identifying a column and a local variable if they are defined a same name. It IS always better practice to define a different naming convention in stored procedures, functions and trigger bodies when dealing with database object names like column.

To cross check, execute following procedure and see the result. 

delimiter // 
drop procedure if exists `same_name_test` //

create procedure `same_name_test`()
begin
   DECLARE cardnumber varchar(20) DEFAULT "";
   -- this statement will print empty string
   SELECT cardnumber as cn 
     FROM card 
    WHERE customer = 'Google';
end;
//
delimiter ;
call `same_name_test`;
+------+
| cn   |
+------+
|      |
+------+

Modified Stored Procedure: 

DELIMITER // 
drop procedure if exists `test` //

CREATE PROCEDURE `test`()
BEGIN 
  DECLARE finished INTEGER DEFAULT 0;
  DECLARE card_number varchar(20) DEFAULT "";
  DECLARE cursor1 CURSOR FOR 
            SELECT cardnumber 
              FROM card_so_q23811277 
             WHERE customer = 'Google';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

  OPEN cursor1;
  get_card: LOOP
    -- read cursor into new local variable
    FETCH cursor1 INTO card_number;
    IF finished = 1 THEN 
      LEAVE get_card;
    END IF;
  END LOOP get_card; 

  -- using new name of variable
  SELECT card_number;

  CLOSE cursor1;
END;

// 

DELIMITER ;

Now call the Stored Procedure: 

CALL `test`;
+-------------+
| card_number |
+-------------+
| 5000        |
+-------------+

Upvotes: 5

Related Questions