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

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

Answer: Insert Using Stored Procedures

Solution

Code explanation