MySQL store function with while loop SELECT

Question

This is my first time writing a MySQL store function but I am having problem see what did I do wrong:

Here is my table and the function:

mysql> 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 > 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);

I expect the SELECT NID2PathName(10) to return "/US/TX/Frisco", but I only get "//". Any help will greatly appreciated.

Does not make sense your logic at WHILE id > 1 DO. Why would you do a loop if all you want bring over is only one value?? — medina
– medina, Commented May 28, 2013 at 23:50

Ike Walker · Accepted Answer · 2013-05-28 23:50:49Z

2

You need to disambiguate the column names from the parameter and variable names.

For example, in this line you use the same name "pid" to reference a variable and a table column, and MySQL is not behaving the way you want it to:

SELECT Name, PID into str, pid FROM PathNames WHERE NID=id LIMIT 1;

I like to use the prefix p_ for parameter names and v_ for variable names in order to guarantee that the variable names and parameter names will not conflict with column names.

Here's your function rewritten with that convention. It should work for you:

DROP FUNCTION IF EXISTS NID2PathName;
DELIMITER //
CREATE FUNCTION NID2PathName(p_nid INT UNSIGNED)
  RETURNS TEXT DETERMINISTIC
BEGIN
  DECLARE v_rtn  TEXT;
  DECLARE v_str  TEXT;
  DECLARE v_tmp  TEXT;
  DECLARE v_id   INT UNSIGNED;
  DECLARE v_pid  INT UNSIGNED;

  SET v_id := p_nid;
  SET v_rtn := "";
  SET v_str := "";
  SET v_tmp := "";

  WHILE v_id > 1 DO
    SELECT Name, PID into v_str, v_pid FROM PathNames WHERE NID=v_id LIMIT 1;
    SET v_rtn := CONCAT('/', v_str, v_rtn);
    set v_id := v_pid;
  END WHILE;

  RETURN v_rtn;
END//
DELIMITER ;

answered May 28, 2013 at 23:50

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

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

2 Comments

TX T Over a year ago

Thank you very much for your advise. I will follow this convention from now. Thanks!

TX T Over a year ago

I have also added this to handle the case where the SELECT return NULL. DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_id := 0;

Collectives™ on Stack Overflow

MySQL store function with while loop SELECT

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related