CODEWITHSUNDEEP
mysqlstored-proceduresdatabase-cursor
PaulP
PaulP

Reputation: 1935

Apparently fetch returns NULL

I am learning stored procedures, cursors in mysql and I stumble on it:

delimiter //

CREATE PROCEDURE some_func()
BEGIN
    DECLARE link_rewrite VARCHAR(255);
    DECLARE link_rewrite_cursor CURSOR FOR SELECT link_rewrite FROM prod;

    OPEN link_rewrite_cursor;

    SET @count = 0;

    WHILE @count < 10 DO
        FETCH link_rewrite_cursor INTO link_rewrite;
        SELECT link_rewrite;
        set @count = @count + 1;
    END WHILE;

    CLOSE link_rewrite_cursor;

END//

delimiter ;

My question is: Why SELECT link_rewrite always returns NULL (in prod table there is 9000 rows). SELECT link_rewrite FROM prod returns a lot of rows(9000 rows).

Upvotes: 9

Views: 5593

Answers (2)

Ike Walker
Ike Walker

Reputation: 65537

You should avoid using the same name for multiple different things. Specifically, give the variable a different name than the column you are selecting. For example, if you rename the variable v_link_rewrite then it will probably work:

delimiter //

DROP PROCEDURE IF EXISTS some_func //

CREATE PROCEDURE some_func()
BEGIN
    DECLARE v_link_rewrite VARCHAR(255);
    DECLARE link_rewrite_cursor CURSOR FOR SELECT link_rewrite FROM prod;

    OPEN link_rewrite_cursor;

    SET @count = 0;

    WHILE @count < 10 DO
        FETCH link_rewrite_cursor INTO v_link_rewrite;
        SELECT v_link_rewrite;
        set @count = @count + 1;
    END WHILE;

    CLOSE link_rewrite_cursor;

END//

delimiter ;

Upvotes: 22

Eric
Eric

Reputation: 95123

If you just want to select the top 10 rows, do this:

select link_rewrite from prod limit 10

It's much quicker and you don't have to go with a cursor.

Upvotes: 0

Related Questions