CODEWITHSUNDEEP
mysqlsqlmysql-workbench
izzak98
izzak98

Reputation: 39

I'm having difficulties calling a stored procedure in another one

I want to call a stored procedure in another one but the syntax doesn't seem to be correct.

I've tried to just skip the process and do the select command directly but that doesn't seem to work and besides I'd like to know how to do this.

DELIMITER //
create procedure Find_Eid(Pid int)
    select EId from Employees inner join Patient on EId = EId_fk where Patient.PId = Pid;
//
DELIMITER ;
call Find_Eid(4);
drop procedure Fill_Interact;
DELIMITER //
create procedure Fill_Interact()
begin
    declare N1 int;
    declare TOT date;
    declare Rid int;
    declare Pid int;
    declare Eid int;  
    set N1 = (select count(*) from Patient);
    set TOT = curdate();
    set Rid = 1;
    set Pid = 1;
    while N1 > 0 do
        set Eid = (call Find_Eid(Pid));
        insert into Interact
        (Time_Of_Treatment, RId_fk, PId_fk, EId_fk)
        values
        (TOT,Rid,Pid, Eid);
        if Rid = (select count(*) from Room limit 1) then
                set Rid = 1;
                set TOT = TOT + 1;
        else
                set Rid = Rid + 1;
        end if;
        set N1 = N1 - 1;
        set Pid = Pid + 1;
    end while;
end;
//
DELIMITER ;
call Fill_Interact();

select * from Interact;

Upvotes: 0

Views: 28

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

If you want to assign the procedures value to another variable, You must use an out parameter in the proc. So your first proc should be - 

DELIMITER //
create procedure Find_Eid(in Pid int, out eid int)
    select EId into eid
    from Employees
    inner join Patient on EId = EId_fk
    where Patient.PId = Pid;
//

Then you can use this proc inside your second procedure - 

drop procedure Fill_Interact;
DELIMITER //
create procedure Fill_Interact()
begin
    declare N1 int;
    declare TOT date;
    declare Rid int;
    declare Pid int;
    declare Eid int;  
    set N1 = (select count(*) from Patient);
    set TOT = curdate();
    set Rid = 1;
    set Pid = 1;
    while N1 > 0 do
        call Find_Eid(Pid, Eid);
        insert into Interact
        (Time_Of_Treatment, RId_fk, PId_fk, EId_fk)
        values
        (TOT,Rid,Pid, Eid);
        if Rid = (select count(*) from Room) then
                set Rid = 1;
                set TOT = TOT + 1;
        else
                set Rid = Rid + 1;
        end if;
        set N1 = N1 - 1;
        set Pid = Pid + 1;
    end while;
end;
//
DELIMITER ;
call Fill_Interact();

Upvotes: 1

Related Questions