Stored Procedure to Get Customer Order History
Stored Procedure to Get Customer Order History
Orders Table:
products Table:
orderdetails Table:
Query Explanation:
● CREATE PROCEDURE GetCustomerOrderHistory(IN inputCustomerID INT)
– This creates a stored procedure called GetCustomerOrderHistory.
– It accepts one input parameter: inputCustomerID, which represents the Customer ID.
● SELECT Orders.OrderID, Orders.OrderDate, Products.ProductName, OrderDetails.Quantity
– This SELECT statement retrieves order details for the given customer:
-
The Order ID
-
The Order Date
-
The Product Name
-
The Quantity of each product in the order
● FROM Orders
– The query starts from the Orders table, which contains overall order information.
● JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
– This JOIN connects the Orders table with OrderDetails to get product-wise quantity for each order.
● JOIN Products ON OrderDetails.ProductID = Products.ProductID
– This JOIN brings in the Products table to get the actual names of the products.
● WHERE Orders.CustomerID = inputCustomerID
– This condition filters the results to show only those orders made by the customer whose ID was passed as input.
● ORDER BY Orders.OrderDate
– This arranges the output by order date so that older orders come first.
● DELIMITER // and DELIMITER ;
– DELIMITER // is used to tell MySQL that the procedure ends with // instead of ;.
– After the procedure is created, we reset the delimiter back to normal using DELIMITER ;.
● CALL GetCustomerOrderHistory(2);
– This line calls the stored procedure.
– Here, 2 is the customer ID whose order history we want to view.
SQL Query:
-- Step 1: Change delimiter so that we can define a procedure
DELIMITER //
-- Step 2: Create the stored procedure
CREATE PROCEDURE GetCustomerOrderHistory(IN inputCustomerID INT)
BEGIN
SELECT
Orders.OrderID,
Orders.OrderDate,
Products.ProductName,
OrderDetails.Quantity
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE Orders.CustomerID = inputCustomerID
ORDER BY Orders.OrderDate;
END //
-- Step 3: Reset delimiter back to normal
DELIMITER ;
-- Step 4: Call the procedure to see order history of Customer ID = 2
CALL GetCustomerOrderHistory(2);
Output:
