Error Re: declaring integer in MySQL stored procedure Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Error Re: declaring integer in MySQL stored procedure without wasting too much if your time.

The question is published on by Tutorial Guruji team.

I’m new to stored procedures and am trying to implement the solution to the question @ Hierarchical Query in MySQL II. I posted my code below. I immediately got hung up on the third line. It looks like my question is answered @ Error declaring integer variable inside MySQL stored function, but I still can’t get it to work.

For my first experiment, I want to query the entire taxonomic tree by designating 1 (the animal kingdom) as the integer.

I’ve been looking at some stored procedure tutorials, but it isn’t yet clear to me exactly how it works, and I may have the cart before the horse. As I understand it, a stored procedure can be queried via MySQL or PHP, and I would prefer to do it in PHP. I don’t understand if the code below is a stored procedure query or something I have to do before I can write queries. I’ve created the practice table (t).

So maybe I should ask my question another way: If I have already have a practice table with a field for Parent ID’s, and I want to learn how to query that table with PHP, do I still need to mess with the code below in order to create a “stored procedure”? Or can I create a stored procedure with a query written in PHP?

DELIMITER $$
create procedure showHierarchyUnder
(
SET i = 1;
)
BEGIN
 declare bDoneYet boolean default false;
 declare working_on int;
 declare next_level int;
 declare theCount int;
CREATE temporary TABLE xxFindChildenxx
(   N int not null,
    processed int not null,
    level int not null,
    parent 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,parent)
        select N,0,next_level,parent
        from t
        where parent=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
??

Answer

This will create the stored procedure for you. In order to set a variable in a stored procedure you must first declare it and only then can you set it after you have declared all your variables. Notice how the SET i = 1; is not in between the () anymore and i has been declared i int;. Looks like you were trying to pass in a variable like create procedure showHierarchyUnder(IN i int(10)) but that would be expecting a value when the procedure is called. You can still do that but if i will always be 1, there is no point. In the future when in doubt just go to dev.mysql.

DELIMITER //
create procedure showHierarchyUnder()
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,
    parent 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,parent)
        select N,0,next_level,parent
        from t
        where parent=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 ;
We are here to answer your question about Error Re: declaring integer in MySQL stored procedure - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji