mysql while loop returning null

Question

I have a table with a persons id as the primary key which contains their manager's id. I want to set up a while loop that will display all of that person's managers ids, all the way to the top of the list. I built a while loop, but it's returning null. What am I doing wrong?

CREATE FUNCTION `whilefunction`() RETURNS varchar(255)
BEGIN
    declare l_loop varchar(25) default '123456';
    declare result varchar(255) default '';
    while l_loop is not null do
        set result = result + (select managerid from table where personid = l_loop);
        set l_loop = (select managerid from table where personid = l_loop);
    end while;
RETURN result;
END

Gordon Linoff · Accepted Answer · 2016-07-26 12:33:44Z

1

You have declared result as a string. Then you use addition. No wonder your code is not doing what you expect.

Perhaps concat() is what you want:

    set result = concat_ws(',', result, (select managerid from table where personid = l_loop);

answered Jul 26, 2016 at 12:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

E. Shock Over a year ago

I'm gonna go ahead and facepalm super hard right now. Thank you so much!

Collectives™ on Stack Overflow

mysql while loop returning null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related