title: Mysql procedure: nesting begin statements in conditional blocks
body: <p>I am trying to only execute a block of code if a variable exists. Here is a code snipit. Can you nest Begin...End statements in an IF block?</p>
  
  <p>I've re-designed this several times. Suggestions?</p>
  
  <pre><code>delimiter //
  
  
  drop trigger if exists example_trigger;//
  create trigger example_trigger AFTER UPDATE on some_table for each row 
  BLOCK1: begin
  
      -- check current status
      DECLARE done BOOLEAN DEFAULT FALSE;
  
  
      -- cap check
      if (new.CURRENT_ALLOCATED &gt;= new.TOTAL_ALLOWED_QTY) then
          SET done = TRUE;
      end if; -- cap check end
  
  
      -- o 
      if (done != TRUE and new.O_KEY is not null and new.A_KEY is null) then
  
          OBLOCK: begin
              DECLARE done_o BOOLEAN DEFAULT FALSE;
              DECLARE pd_nbr INT;
              DECLARE no_more_rows BOOLEAN;
              DECLARE cur_pd CURSOR FOR 
                  select pd.STATUS_KEY
                  from PROD_DEL_V pd
                      join PROD_T p on pd.KEY_NBR = p.KEY_NBR
                  where pd.STATUS not in ('PU', 'EX')
                      and p.O_KEY = new.O_KEY;
              DECLARE CONTINUE HANDLER FOR NOT FOUND
                  SET done_o = TRUE;  
  
              -- run updates
              if (done_o != TRUE) then
  
                  -- open cursor
                  OPEN cur_pd;        
  
                  -- loop start
                  loop_it: LOOP
  
                      FETCH cur_pd INTO pd_nbr;
  
                      -- exit loop if..
                      if no_more_rows = TRUE THEN 
                          CLOSE cur_pd;
                          LEAVE loop_it;
                      end if;
  
  
                      INSERT INTO STATUS_TABLE (
                          STATUS_KEY
                          , STATUS
                          , NOTE_TXT
                          )
                          (
                          SELECT 
                              PD.STATUS_KEY
                              , 'PU' AS STATUS
                              , concat('example_trigger - MAX has been reached or exceeded [TOTAL_ALLOWED_QTY = ',new.TOTAL_ALLOWED_QTY,' and CURRENT_ALLOCATED = ', new.CURRENT_ALLOCATED, ']') AS NOTE_TXT
                          FROM  PROD_DEL_TABLE PD 
                          WHERE PD.STATUS_KEY = pd_nbr
                          );  
  
                  END LOOP loop_it;
  
              end if; -- run updates end
  
          end OBLOCK:; -- end block
  
      end if; -- o 
  
  
      -- a
      if (done != TRUE and new.O_KEY is null and new.A_KEY is not null) then
  
          ABLOCK: begin
              DECLARE done_a BOOLEAN DEFAULT FALSE;
              DECLARE pd_nbr INT;
              DECLARE no_more_rows BOOLEAN;
              DECLARE cur_pd CURSOR FOR 
                  select pd.STATUS_KEY
                  from PROD_DEL_V pd
                      join PROD_T p on pd.KEY_NBR = p.KEY_NBR
                      join A_O_T a on a.O_KEY = p.O_KEY
                  where pd.STATUS not in ('PU', 'EX')
                      and a.A_KEY = new.A_KEY;
              DECLARE CONTINUE HANDLER FOR NOT FOUND
                  SET done_a = TRUE;
  
              -- run updates
              if (done_a != TRUE) then
  
                  -- open cursor
                  OPEN cur_pd;        
  
                  -- loop start
                  loop_it: LOOP
  
                      FETCH cur_pd INTO pd_nbr;
  
                      -- exit loop if..
                      if no_more_rows = TRUE THEN 
                          CLOSE cur_pd;
                          LEAVE loop_it;
                      end if;
  
  
                      INSERT INTO STATUS_TABLE (
                          STATUS_KEY
                          , STATUS
                          , NOTE_TXT
                          )
                          (
                          SELECT 
                              PD.STATUS_KEY
                              , 'PU' AS STATUS
                              , concat('example_trigger - MAX has been reached or exceeded [TOTAL_ALLOWED_QTY = ',new.TOTAL_ALLOWED_QTY,' and CURRENT_ALLOCATED = ', new.CURRENT_ALLOCATED, ']' AS NOTE_TXT
                          FROM  PROD_DEL_TABLE PD 
                          WHERE PD.STATUS_KEY = pd_nbr
                          );  
  
                  END LOOP loop_it;
  
              end if; -- run updates end
  
          end ABLOCK; -- end block
  
      end if; -- a
  end BLOCK1; -- end large block
  //
  
  delimiter ;
  </code></pre>