Help with Stored Procedure Script

[Chavista]

I discovered MySQL’s stored procedure feature just a few days ago and am hooked. But I’m pulling my hair out trying to figure out how to create one. In fact, I somehow created a working stored procedure, even though it was preceded by an error message. But it stopped working after I tried to modify it in phpMyAdmin.

All my attempts to recreate it fail. It’s especially confusing trying to figure out whether or not to use delimiters, which delimiters to use and whether I should post any code before or after delimiters. As I recall, I created my working model by DELETING the last delimiter, “END” or something like that.

So I wondered if anyone could show me the proper code.

The table I want to target is gz_life_mammals. It features the names of animals in a parent-child-relationship (fields Taxon and Parent), like this;

Taxon | Parent | ParentID
Mammalia | Chordata | 1
Carnivora | Mammalia | 2
Felidae | Carnivora | 3
Panthera | Felidae | 4
Panthera leo | Panthera | 5

My goal is to simply display the number of children, grandchildren, great grandchildren, etc. for every taxon. In other words, Mammalia has 29 orders, 234 families, 521 genera and 8,000 species, or whatever.

So I added the field ParentID, with numerical ID’s for each taxonomic level.

I’ve lost the script I used to create my original working model. However, the script below is similar, though I’ve modified it to work with my new table, replacing field parent with ParentID, for example.

One frequent error I get is 1054 "unknown column - theID.) That appears to relate to this line:

select theId,0,0,0;

I think theID was originally associated with “SET i = 1” somehow. I’m not sure if I need to modify that line, delete theID or what. I don’t understand how it works well enough.

Thanks for any tips.

DELIMITER //
create procedure Mammals()
BEGIN
 declare bDoneYet boolean default false;
 declare working_on int;
 declare next_level int;
 declare theCount int;
 declare i int;
 SET i = 1;
CREATE temporary TABLE xxFindChildenxx
(   N int not null,
    processed int not null,
    level int not null,
    ParentID int not null
);
set bDoneYet=false;
insert into xxFindChildenxx (N,processed,level,parent) select theId,0,0,0;
while (!bDoneYet) do
    select count(*) into theCount from xxFindChildenxx where processed=0;

    if (theCount=0) then 
        set bDoneYet=true;
    else
        SELECT N,level+1 INTO working_on,next_level FROM xxFindChildenxx where processed=0 limit 1;
        insert into xxFindChildenxx (N,processed,level,ParentID)
        select N,0,next_level,ParentID
        from t
        where ParentID=working_on;
        update xxFindChildenxx set processed=1 where N=working_on;
    end if;
end while;
delete from xxFindChildenxx where N=theId;
select level,count(*) as lvlCount from xxFindChildenxx group by level;
drop table xxFindChildenxx;
END//
DELIMITER ;