Procedure for batch price update with audit

Procedure for batch price update with audit

Hard SQL Stored Procedures 15 views
Explanation Complexity

Problem Statement

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

Example

Products, PriceAudit
Procedure created

Constraints

Use set-based insert-select

Input / Output Format

Input Format
SQL procedure
Output Format
Procedure created
Constraints
Use set-based insert-select

Examples

Input:
Products, PriceAudit
Output:
Procedure created

Example Solution (Public)

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

Official Solution Code

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

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.