CODEWITHSUNDEEP
mysqlstored-procedures
Imran
Imran

Reputation: 3072

Store procedure doesn't return mutiple rows

I just want to return multiple row using CURSOR of procedure. But It return empty value. I've used a simple select query for test purpose. 

   CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user`()
        READS SQL DATA
    BEGIN
        DECLARE id INT;
        DECLARE name VARCHAR (256);

        DECLARE done int default 0;

        DECLARE curl CURSOR FOR

        SELECT id, name FROM user; 

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;

        OPEN curl;
            data_loop:LOOP
                FETCH curl INTO id, name;
                IF done=1 THEN 
                    leave data_loop;
                END IF;
            END LOOP data_loop;
        CLOSE curl;
    END

Upvotes: 0

Views: 34

Answers (1)

Joe Taras
Joe Taras

Reputation: 15389

A procedure must terminated (about multiple rows) with a SELECT.

But I can't show your final SELECT.

For example, you can use a temporary table USERS2 (CREATE TEMPORARY TABLE users2) with the same fields of USER table. In the loop you can write in USERS2 (INSERT INTO users2 ... and so on), so at the end you'll write:

SELECT * FROM users2

Upvotes: 1

Related Questions