Dynamic compound statement

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