SQL Interview Preparation—Intermediate Level/

...

/

Answer: Update Using Stored Procedures

Answer: Update Using Stored Procedures

Find a detailed explanation of updating records using stored procedures in SQL.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- Query to update records using stored procedure
DELIMITER $$
CREATE PROCEDURE DiscountCategoryProducts(IN catID INT,
                                          IN discountPercentage DECIMAL(5, 2))
BEGIN
    UPDATE Products AS P
    SET    P.Price = P.Price * (1 - discountPercentage / 100)
    WHERE  P.CategoryID = catID;
END $$
DELIMITER ;
-- Execute stored procedure
CALL DiscountCategoryProducts(1, 10);
-- View the result
SELECT * FROM Products P WHERE P.CategoryID = 1;

Code explanation

The explanation of the solution code is given below:

  • Line 2: The DELIMITER $$ changes the statement delimiter to $$ so semicolons can be used within the procedure.

  • Lines 4–5: The CREATE PROCEDURE defines a stored procedure called DiscountCategoryProducts with two parameters: catID and discountPercentage.

  • Lines 6–10: The BEGIN and END are used to define the body of the stored procedure. The UPDATE statement updates the existing record in the Products table with new values for prices.

  • Line 12: The DELIMITER ; resets the statement delimiter back to the default semicolon (;).

  • Line 15: The CALL statement executes the stored procedure, passing 1 and 10 as ...