Create a MySQL Stored Procedure to Modify Employee Salary

Create a Stored Procedure to Update Employee Salary

Write a MySQL query to create a stored procedure that updates an employee's salary.

Solution:

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

-- Create a stored procedure named `UpdateEmployeeSalary`
CREATE PROCEDURE UpdateEmployeeSalary(
    IN p_EmployeeID INT,          -- Input parameter: Employee ID
    IN p_NewSalary DECIMAL(10,2)  -- Input parameter: New salary amount
)
BEGIN
    -- Update the salary of the employee with the given EmployeeID
    UPDATE Employees 
    SET Salary = p_NewSalary 
    WHERE EmployeeID = p_EmployeeID;
END //

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

Explanation:

• Purpose of the Query:
• The goal is to automate salary updates using a stored procedure.
• Key Components:
• UPDATE: Modifies the employee's salary.
• WHERE: Specifies the employee to update.
• Why use Stored Procedures?:
• Stored procedures ensure consistent and secure updates to the database.
• Real-World Application:
• For example, in a payroll system, you might use a stored procedure to adjust salaries.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a stored procedure that updates a department's budget in the Departments table.
• Write a MySQL query to create a stored procedure that updates a project's deadline in the Projects table.
• Write a MySQL query to create a stored procedure that updates a customer's address in the Customers table.
• Write a MySQL query to create a stored procedure that updates an order's status in the Orders table.

Go to:

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

PREV : Call the Stored Procedure to Insert an Employee
NEXT : Call the Stored Procedure to Update Salary.

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