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