Procedure to place an order with items table variable

Procedure to place an order with items table variable

Hard SQL Stored Procedures 15 views
Explanation Complexity

Problem Statement

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

Example

TempOrderItems(product_id, qty, price), Orders, OrderItems
Procedure created

Constraints

Assume TempOrderItems already filled

Input / Output Format

Input Format
SQL procedure
Output Format
Procedure created
Constraints
Assume TempOrderItems already filled

Examples

Input:
TempOrderItems(product_id, qty, price), Orders, OrderItems
Output:
Procedure created

Example Solution (Public)

SQL 
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;

Official Solution Code

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;
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.