Write a Stored Procedure to Count Employees in a Department

Create a Stored Procedure to Count Employees in a Department

Write a MySQL query to create a stored procedure that counts the number of employees in a specific department.

Solution:

-- Change the delimiter to allow defining a multi-line stored procedure
DELIMITER //

-- Create a stored procedure named `CountEmployeesInDepartment`
CREATE PROCEDURE CountEmployeesInDepartment(
    -- Input parameter: Department ID to filter employees
    IN p_DepartmentID INT,  
    -- Output parameter: Stores the total count of employees in the department
    OUT p_EmployeeCount INT  
)
BEGIN
    -- Count the number of employees in the given department
    SELECT COUNT(*) INTO p_EmployeeCount  
    FROM Employees  
    WHERE DepartmentID = p_DepartmentID;  
END //  

-- Reset the delimiter back to default `;`
DELIMITER ;

Explanation:

• Purpose of the Query:
• The goal is to automate the counting of employees in a department using a stored procedure.
• Key Components:
• SELECT COUNT(*) INTO: Counts and stores the number of employees.
• OUT: Specifies an output parameter.
• Why use Stored Procedures?:
• Stored procedures encapsulate complex queries, making them reusable.
• Real-World Application:
• For example, in a HR system, you might use a stored procedure to count employees in each department.

For more Practice: Solve these Related Problems:

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

Go to:

• Master MySQL Stored Procedures and Triggers Exercises Home. ↩
• MySQL Exercises Home ↩

PREV : Create a Trigger to Update Last Modified Date.
NEXT : Call the Stored Procedure to Count Employees.

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