Write a Stored Procedure to Compute Total Salary

Stored Procedure to Calculate Total Salary by Department

Write a MySQL query to create a stored procedure that calculates the total salary for a specific department.

Solution:

-- Set the delimiter to `//` to allow multi-line procedure definition
DELIMITER //

-- Create a stored procedure named `CalculateTotalSalaryByDepartment`
CREATE PROCEDURE CalculateTotalSalaryByDepartment(
    IN p_DepartmentID INT,  -- Input parameter: Department ID to filter employees
    OUT p_TotalSalary DECIMAL(10, 2)  -- Output parameter: Total salary of the department
)
BEGIN
    -- Calculate the total salary for the specified department
    SELECT SUM(Salary) INTO p_TotalSalary  
    FROM Employees  
    WHERE DepartmentID = p_DepartmentID;  -- Filter by the given department ID
END //  -- End of the procedure definition

-- Reset the delimiter to `;` for normal MySQL execution
DELIMITER ; 

Explanation:

• Purpose of the Query:
• The goal is to automate the calculation of total salary for a department using a stored procedure.
• Key Components:
• SELECT SUM(Salary) INTO: Calculates and stores the total salary.
• OUT: Specifies an output parameter.
• Why use Stored Procedures?:
• Stored procedures encapsulate complex calculations, making them reusable.
• Real-World Application:
• For example, in a payroll system, you might use a stored procedure to calculate total salaries by department.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a stored procedure that calculates the total budget for a specific department.
• Write a MySQL query to create a stored procedure that calculates the total revenue for a specific product category.
• Write a MySQL query to create a stored procedure that calculates the total sales for a specific customer.
• Write a MySQL query to create a stored procedure that calculates the total cost for a specific project.

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

Previous MySQL Exercise: Create a Trigger to Prevent Deleting Active Employees.
Next MySQL Exercise: Call the Stored Procedure to Calculate Total Salary.