Stored Procedure to Insert a New Product
Stored Procedure to Insert a New Product
Products Table:
Query Explanation:
-
USE SalesInventoryDB;
Tells MySQL to use theSalesInventoryDBdatabase. -
DELIMITER //
Temporarily changes the command delimiter from;to//so that the full procedure body can be defined without breaking prematurely. -
CREATE PROCEDURE InsertNewProduct(...)
Defines a stored procedure namedInsertNewProductthat takes six input parameters (ProductName, CategoryID, etc.). -
BEGIN ... END
The block where actual SQL logic is written for the procedure. -
INSERT INTO Products(...) VALUES (...);
Inserts the values passed to the procedure into theProductstable. -
DELIMITER ;
Resets the delimiter back to default;. -
CALL InsertNewProduct(...)
Executes the procedure and inserts a new row into theProductstable.
SQL Query:
USE SalesInventoryDB;
-- Change the delimiter to define the procedure properly
DELIMITER //
CREATE PROCEDURE InsertNewProduct(
IN p_ProductName VARCHAR(100),
IN p_CategoryID INT,
IN p_SupplierID INT,
IN p_UnitPrice DECIMAL(10,2),
IN p_UnitsInStock INT,
IN p_Discontinued TINYINT
)
BEGIN
-- Insert new product record into the Products table
INSERT INTO Products(ProductName, CategoryID, SupplierID, UnitPrice, UnitsInStock, Discontinued)
VALUES (p_ProductName, p_CategoryID, p_SupplierID, p_UnitPrice, p_UnitsInStock, p_Discontinued);
END //
-- Reset the delimiter to default
DELIMITER ;
-- Call the procedure to insert a product
CALL InsertNewProduct('Gaming Mouse', 1, 2, 1500.00, 100, 0);
Output:
