title: MySQL store function with while loop SELECT
body: <p>This is my first time writing a MySQL store function but I am having problem see what did I do wrong:</p>
  
  <p>Here is my table and the function:</p>
  
  <pre><code>mysql&gt; select * from PathNames;
  +-----+--------+-----+
  | NID | Name   | PID |
  +-----+--------+-----+
  |   1 | /      |   1 |
  |   6 | US     |   1 |
  |   7 | TX     |   6 |
  |   8 | Dallas |   7 |
  |   9 | Plano  |   7 |
  |  10 | Frisco |   7 |
  +-----+--------+-----+
  
  
  DROP FUNCTION IF EXISTS NID2PathName;
  DELIMITER //
  CREATE FUNCTION NID2PathName(nid INT UNSIGNED)
    RETURNS TEXT DETERMINISTIC
  BEGIN
    DECLARE rtn  TEXT;
    DECLARE str  TEXT;
    DECLARE tmp  TEXT;
    DECLARE id   INT UNSIGNED;
    DECLARE pid  INT UNSIGNED;
  
    SET id  = nid;
    SET rtn = "";
    SET str = "";
    SET tmp = "";
  
    WHILE id &gt; 1 DO
      SELECT Name, PID into str, pid FROM PathNames WHERE NID=id LIMIT 1;
      SET rtn = CONCAT('/', str, rtn);
      set id = pid;
    END WHILE;
  
    RETURN rtn;
  END//
  DELIMITER ;
  
  SELECT NID2PathName(10);
  </code></pre>
  
  <p>I expect the <code>SELECT NID2PathName(10)</code> to return <code>"/US/TX/Frisco"</code>, but I only get <code>"//"</code>.  Any help will greatly appreciated. </p>