Is it good practice to call stored proc inside stored proc
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.