Procedure to place an order with items table variable
SQL
Hard
2 views
Problem Description
Create sp_place_order(p_customer_id, p_order_id) that inserts an order, then inserts all rows from TempOrderItems into OrderItems for that order in one go.
Input Format
SQL procedure
Output Format
Procedure created
Sample Test Case
Input:
TempOrderItems(product_id, qty, price), Orders, OrderItems
Output:
Procedure created
Constraints
Assume TempOrderItems already filled
Official Solution
CREATE PROCEDURE sp_place_order(IN p_customer_id INT, IN p_order_id INT) BEGIN INSERT INTO Orders(order_id, customer_id, order_date, status, total_amount) VALUES (p_order_id, p_customer_id, CURRENT_DATE, 'NEW', 0); INSERT INTO OrderItems(order_id, product_id, qty, price) SELECT p_order_id, product_id, qty, price FROM TempOrderItems; END;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!