Procedure for batch price update with audit
SQL
Hard
2 views
Problem Description
Create sp_discount_category(p_category, p_percent) that updates product prices and writes an audit row per product into PriceAudit(product_id, old_price, new_price, changed_at).
Input Format
SQL procedure
Output Format
Procedure created
Sample Test Case
Input:
Products, PriceAudit
Output:
Procedure created
Constraints
Use set-based insert-select
Official Solution
CREATE PROCEDURE sp_discount_category(IN p_cat VARCHAR(100), IN p_percent DECIMAL(5,2)) BEGIN INSERT INTO PriceAudit(product_id, old_price, new_price, changed_at) SELECT product_id, price, price - (price * p_percent / 100), CURRENT_TIMESTAMP FROM Products WHERE category = p_cat; UPDATE Products SET price = price - (price * p_percent / 100) WHERE category = p_cat; END;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!