AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

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. ...

Ask