Search⌘ K
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Insert Using Stored Procedures

Understand how to create and use stored procedures in SQL for inserting data across related tables. Explore parameterized procedures, variable declaration, and multiple insert statements. This lesson also covers alternatives like triggers, user-defined functions, and prepared statements to manage data insertion tasks.

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 ...