MySQL Forums
Forum List  »  Stored Procedures

Is it good practice to call stored proc inside stored proc
Posted by: Kaushik Shete
Date: May 04, 2021 08:19AM

Is good practice to call stored proc inside stored proc which is from another Db? If not, why and what will be the consequences?
e.g.
EmployeeDb (which stores Employee Information) -> sp_InsertEmployeeDetails
Table EmployeeDetail (Id primary key, Name, Age)

SalaryDb (which stores Salary Information) -> sp_InsertEmployeeSalary
Table SalaryDetail (Id primary key, salary, EmployeeId (indirect ref. of Id from EmployeeDb))

-- sp_InsertSalaryDetails
create procedure sp_InsertSalaryDetails( EmpId, Salary)
begin

set @pval=uuid();
insert into SalaryDetail(Id, Salary, EmployeeId) values
(@pval, Salary, EmpId);

end

-- sp_InsertEmployeeDetails
create procedure sp_InsertEmployeeDetails(Name, Age, Salary)
begin

set @pkval=uuid();
insert into EmployeeDetail(Id, Name, Age) values (@pkval,..);

call SalaryDb.sp_InsertEmployeeSalary(@pkval,Salary,...)

end

stored proc call:-
call EmployeeDb.sp_InsertEmployeeDetails(Name, Age, Salary);

Note:-
1. There are 2 Database named as SalaryDb and EmployeeDb.

Options: ReplyQuote


Subject
Views
Written By
Posted
Is it good practice to call stored proc inside stored proc
72
May 04, 2021 08:19AM


Sorry, only registered users may post in this forum.

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.