SQL loop get a null value

Question

In the next portion of code i used a cursor and a loop to generate a list of users who match the search query, but i get a null value, why ?

BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE ids TEXT;
    DECLARE response TEXT;
    DECLARE ids_cursor CURSOR FOR
        SELECT id 
        FROM user WHERE email LIKE CONCAT('%',value,'%') 
        OR lastname LIKE CONCAT('%',value,'%') 
        OR name LIKE CONCAT('%',value,'%');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 2;

    OPEN ids_cursor;
    get_ids: LOOP

        FETCH ids_cursor INTO ids;

        IF finished = 2 THEN
            LEAVE get_ids;
        END IF;

        SET response = CONCAT(ids,";",response);

    END LOOP get_ids;
    CLOSE ids_cursor;

    RETURN response;
END

i call the function with select name_of_function('test') as response; — Robert-Dan
– Robert-Dan, Commented Jan 10, 2015 at 9:11
are you sure the query is succeeding? try set response to 'not foudn' or similar in the IF finished=2 block to debug that. — Trent Lloyd
– Trent Lloyd, Commented Jan 10, 2015 at 11:30
also, you can do this with GROUP_CONCAT: dev.mysql.com/doc/refman/5.6/en/group-by-functions.html — Trent Lloyd
– Trent Lloyd, Commented Jan 10, 2015 at 11:31

Saharsh Shah · Accepted Answer · 2015-01-10 14:30:31Z

1

You do not need to use user defined function you can direcltly fetch the IDs using GROUP_CONCAT() function by below query.

SELECT GROUP_CONCAT(id SEPARATOR ';') AS response
FROM user 
WHERE email LIKE CONCAT('%',value,'%') 
   OR lastname LIKE CONCAT('%',value,'%') 
   OR name LIKE CONCAT('%',value,'%');

answered Jan 10, 2015 at 14:30

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL loop get a null value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related