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:
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.