Create a MySQL Stored Procedure for Employee insertion

Create a Stored Procedure to Insert a New Employee

Write a MySQL query to create a stored procedure that inserts a new employee into the Employees table.

Solution:

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

-- Create a stored procedure named `InsertEmployee`
CREATE PROCEDURE InsertEmployee(
    IN p_Name VARCHAR(100),       -- Input parameter: Employee name
    IN p_DepartmentID INT,        -- Input parameter: Department ID
    IN p_Salary DECIMAL(10,2)     -- Input parameter: Employee salary
)
BEGIN
    -- Insert the new employee into the Employees table
    INSERT INTO Employees (Name, DepartmentID, Salary)
    VALUES (p_Name, p_DepartmentID, p_Salary);
END //

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

Explanation:

• Purpose of the Query:
• The goal is to automate the insertion of new employees using a stored procedure.
• This demonstrates how to create and use stored procedures.
• Key Components:
• CREATE PROCEDURE: Defines the stored procedure.
• IN: Specifies input parameters.
• INSERT INTO: Inserts data into the table.
• Why use Stored Procedures?:
• Stored procedures encapsulate business logic, making it reusable and easier to maintain.
• Real-World Application:
• For example, in a HR system, you might use a stored procedure to add new employees.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a stored procedure that inserts a new department into the Departments table.
• Write a MySQL query to create a stored procedure that inserts a new project into the Projects table.
• Write a MySQL query to create a stored procedure that inserts a new customer into the Customers table.
• Write a MySQL query to create a stored procedure that inserts a new order into the Orders table.

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

Previous MySQL Exercise: Stored Procedures and Triggers Exercises Home
Next MySQL Exercise: Call the Stored Procedure to Insert an Employee.