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:
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.
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
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
MySQL Built-in Functions
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development