Calling Stored Programs from Stored Programs

Calling one stored program from another is perfectly simple. You do this with the CALL statement, just as you would from the MySQL command-line client.

Figure 2-15 shows a simple stored procedure that chooses between two stored procedures based on an input parameter. The output of the stored procedure (l_bonus_amount is populated from an OUT parameter) is passed to a third procedure.

Here is an explanation of the significant lines:

Line(s)	Explanation
11	Determine if the employee is a manager. If he is a manager, we call the calc_manager_bonus stored procedure; if he is not a manager, we call the calc_minion_bonus stored procedure.
12 and 14	With both stored procedures, pass in the employee_id and provide a variablel_bonus_amountto receive the output of the stored procedure.
16	Call the grant_bonus stored procedure that passes as arguments the employee_id and the bonus amount, as calculated by the stored procedure we called in line 12 or 14.

Figure 2-15. Example of calling one stored procedure from another