Execute a Stored Procedure to Retrieve Employee Count

Call the Stored Procedure to Count Employees

Write a MySQL query to call the CountEmployeesInDepartment stored procedure and retrieve the employee count.

Solution:

-- Declare a variable to store the employee count and initialize it to 0
SET @employee_count = 0;

-- Call the `CountEmployeesInDepartment` stored procedure with DepartmentID = 2
-- The result will be stored in the `@employee_count` variable
CALL CountEmployeesInDepartment(2, @employee_count);

-- Retrieve and display the employee count as `EmployeeCount`
SELECT @employee_count AS EmployeeCount;

Explanation:

• Purpose of the Query:
• The goal is to execute the stored procedure and retrieve the employee count.
• Key Components:
• CALL: Executes the stored procedure.
• SELECT @employee_count: Retrieves the output parameter.
• Why use Stored Procedures?:
• Stored procedures simplify complex operations and improve code reusability.
• Real-World Application:
• For example, in a HR system, you might call a stored procedure to count employees in a department.

For more Practice: Solve these Related Problems:

• Write a MySQL query to call a stored procedure that counts the number of customers in a specific city.
• Write a MySQL query to call a stored procedure that counts the number of orders for a specific customer.
• Write a MySQL query to call a stored procedure that counts the number of products in a specific category.
• Write a MySQL query to call a stored procedure that counts the number of projects in a specific department.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Create a Stored Procedure to Count Employees in a Department.
Next MySQL Exercise: Create a Trigger to Prevent Deleting Active Employees.