Search⌘ K
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Insert Using Stored Procedures

Find a detailed explanation of inserting data in tables using stored procedures in SQL.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- Query with stored procedure named AddEmployeeWithCategory 
-- and parameters are EmployeeName, EmployeeAge, and ProductCategoriesName
DELIMITER $$
CREATE PROCEDURE AddEmployeeWithCategory (IN EmployeeName VARCHAR(50), 
                                          IN EmployeeAge INT,
                                          IN ProductCategoriesName VARCHAR(50) )
BEGIN
    DECLARE     lastID INT;
    INSERT INTO Employees (EName, Age)
    VALUES      (EmployeeName, EmployeeAge);
    
    SET         lastID = LAST_INSERT_ID();
    INSERT INTO ProductCategories (CategoryName, EID)
    VALUES      (ProductCategoriesName, lastID);
END $$
DELIMITER ;
-- Execute the stored procedure
CALL AddEmployeeWithCategory('Jane', 30, 'Jackets');
-- Show the recently added data
SELECT E.EName, E.Age, C.CategoryName
FROM   Employees E 
JOIN   ProductCategories C ON E.EID = C.EID
WHERE  E.EName = 'Jane';

Code explanation

The explanation of the solution code is given below:

  • Line 3: The DELIMITER $$ changes the statement delimiter to $$ so semicolons can be used within the procedure.

  • Lines 5–7: The CREATE PROCEDURE defines a stored procedure called AddEmployeeWithCategory with three parameters: EmployeeName, EmployeeAge, and ProductCategoriesName.

  • Lines 8–16: The BEGIN and END are used to define the body of the stored procedure. The DECLARE statement creates a variable lastID to store the ID of the last inserted employee. The INSERT INTO statement adds a new row to the Employees table with the input parameters. The SET statement stores the last inserted employee’s ID in lastID, and INSERT INTO statement adds a new row in the ProductCategories table.

  • Line 18: The DELIMITER ; resets the statement delimiter back to the default semicolon (;).

  • Line 21: The CALL statement executes the stored procedure, passing 'Jane', 30 ...