CODEWITHSUNDEEP
mysql
TickleMonster
TickleMonster

Reputation: 107

Cursor incorrect integer value error

I am trying to make my first cursor in MySQL and I am receiving an error. It says incorrect integer value. I was thinking this would grab the value in row one from column customer_Id, and store it into the IdValue variable. How do I code this correctly and fix this error?

DELIMITER $$

CREATE PROCEDURE CursorProcedure()
BEGIN
DECLARE IdValue int;
DECLARE  myCursor CURSOR FOR 
SELECT customer_Id FROM customers;

OPEN myCursor;
FETCH myCursor INTO IdValue;
CLOSE myCursor;
SELECT IdValue;
END$$
DELIMITER ;

Upvotes: 3

Views: 181

Answers (1)

Drew
Drew

Reputation: 24949

DROP PROCEDURE IF EXISTS CursorProcedure;
DELIMITER $$
CREATE PROCEDURE CursorProcedure()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE IdValue int;
    DECLARE myCursor CURSOR FOR SELECT customer_Id FROM customers;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN myCursor;
    read_loop: LOOP
        FETCH myCursor INTO IdValue;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 
        --  YOU ARE IN YOUR READ LOOP
        --  DO SOMETHING WITH IT HERE
        --
    END LOOP;
    CLOSE myCursor;
END$$
DELIMITER ;

But in the "do something with it here", don't do a SELECT on it because it will generate multiple result sets. Do something meaningful.

Manual page on Cursors.

As an aside, cursors are rarely your friends. They are extremely slow. Use them in dire emergencies only.

Upvotes: 2

Related Questions