Hi All,
I have a problem with an stored procedure and I swear that I can't give it a solution. The SP search in a table for categories, the parents of an specific category... so, it should iterate in the category table.
This is the SP:
DROP PROCEDURE IF EXISTS getCategoryParents;
DELIMITER $$
CREATE PROCEDURE `getCategoryParents`(CodigoIn INT)
BEGIN
DECLARE entrar Bool;
DECLARE padreTmp int;
DECLARE codigos Varchar(100);
DECLARE primera Bool;
SET primera = true;
SET entrar = true;
SET codigos ='';
WHILE entrar DO
IF ((SELECT padre FROM categoria WHERE codigo = CodigoIn) = 0) THEN
SET entrar = false;
SET codigos = CONCAT(codigos, ' , ' , CodigoIn);
SELECT * FROM Categoria WHERE Codigo IN (codigos);
else
IF primera THEN
SET primera = false;
else
IF codigos = '' THEN
SET codigos = CodigoIn;
else
SET codigos = CONCAT(codigos, ' , ' , CodigoIn);
end IF;
end IF;
SET CodigoIn = (SELECT padre FROM categoria WHERE codigo = CodigoIn);
end IF;
end WHILE;
end
Those are the registers to you could make an idea:
Code - Name - Parent
1 - Market - 0
9 - toilet - 1
10 - Personal Toilet - 9
I call the procedure like this: CALL getCategoryParents(10)
And it returns: 9 - toilet - 1
It should return the codes 9 and 1.
If I print the content of 'Codigos' before the final select, it have: 9 , 1
And I made the query apart... like this: SELECT * FROM Categoria WHERE Codigo IN ( 9 , 1 );
And the return is:
1 Mercado 0
9 Aseo 1
What I really expected. Why???
Thanks a lot