Dynamic compound statement
A dynamic compound statement is similar to an SQL procedure except that it does not require a permanent object to be created. For example, a dynamic compound statement can be used to add logic to scripts.
When a dynamic compound statement is executed, it has the overhead of creating, executing, and dropping a program. Because of this overhead, an SQL procedure should be used for situations where the statement needs to be run frequently. There are no input or output parameters for a dynamic compound statement; you can use global variables instead to pass input values and return values.
Suppose you have a script that needs to set up a table that contains constant values. For example, you have a table that has a row for every day of the year (integers 1 through 366).
CREATE TABLE day_numbers (day_value INT)
If you don't know that the table contains all the correct values, you need to delete all the rows and insert them again. By introducing a compound (dynamic) statement in the script, when the table is already built correctly, it does not need to be repopulated.
BEGIN
DECLARE day_count INT;
DECLARE unique_day_count INT DEFAULT 0;
DECLARE insert_cnt INT;
DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE '42704'
/* Handle table does not exist error */
CREATE TABLE day_numbers (day_value INT);
SELECT COUNT(DISTINCT day_value) , COUNT(day_value)
INTO unique_day_count, day_count
FROM day_numbers;
IF day_count = 366 AND unique_day_count = 366 THEN
BEGIN END; /* Table correctly populated */
ELSE
BEGIN
DELETE FROM day_numbers; /* Remove all rows */
SET insert_cnt = 1;
WHILE insert_cnt < 367 DO
INSERT INTO day_numbers VALUES insert_cnt;
SET insert_cnt = insert_cnt + 1;
END WHILE;
END;
END IF;
END