MySQL Forums
Forum List  »  Stored Procedures

Stored Procedure returning what I didn't expected
Posted by: Jorge Serrate
Date: September 29, 2014 01:42PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Stored Procedure returning what I didn't expected
2356
September 29, 2014 01:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.