Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Execution Blocks

Explore how to use execution blocks within stored procedures and triggers to manage complex SQL logic. Understand delimiter usage for defining routines, the difference between execution blocks and transactions, and how triggers update data automatically during table modifications. This lesson equips you with practical skills to create, execute, and troubleshoot execution blocks in advanced SQL scenarios.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
-- Procedure using an execution block
DELIMITER //
CREATE PROCEDURE GetTotalStockValue()
BEGIN
    DECLARE total_value DECIMAL(10,2);
    SELECT SUM(Price * Stock) INTO total_value FROM Products;
    SELECT total_value AS TotalStockValue;
END //
-- Trigger using an execution block
CREATE TRIGGER UpdateRestockDate
BEFORE UPDATE ON Products
FOR EACH ROW
BEGIN
    IF NEW.Stock <> OLD.Stock THEN
        SET NEW.LastRestockDate = CURDATE();
    END IF;
END //
DELIMITER ;
-- Execute the procedure to view total inventory value
CALL GetTotalStockValue();
-- Test the trigger by updating a product’s stock
UPDATE Products
SET    Stock = Stock + 1
WHERE  ProductID = 1;
-- Verify that the LastRestockDate has updated
SELECT ProductID, LastRestockDate
FROM   Products
WHERE  ProductID = 1;
-- Optional cleanup
DROP PROCEDURE IF EXISTS GetTotalStockValue;
DROP TRIGGER IF EXISTS UpdateRestockDate;

Explanation

The explanation of the solution code is given below:

  • Line 2: The DELIMITER // command sets a temporary delimiter so that semicolons within BEGIN ... END blocks do not prematurely terminate the routine definitions. This is essential when defining stored procedures or triggers in MySQL.

  • Lines 4–9: A stored procedure GetTotalStockValue is created using an execution block.

    • DECLARE total_value DECIMAL(10,2); defines a local variable to hold the result.

    • SELECT SUM(Price * Stock) INTO total_value FROM Products; calculates the total inventory value across all rows.

    • SELECT total_value AS TotalStockValue; returns the value to the caller.

    • The block ends with END // using the custom delimiter.

  • Lines 12–19: A trigger UpdateRestockDate is defined to run before any update on the Products table.

    • FOR EACH ROW ensures the logic runs individually for every modified row.

    • Inside the execution block, IF NEW.Stock <> ...