CREATE DEFINER root localhost PROCEDURE AddWordDictionary IN name_d VA

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddWordDictionary`(IN `name_d` VARCHAR(20), IN `name_w` VARCHAR(20))
BEGIN
  
    DECLARE id_d INT;
  	DECLARE id_w INT;
 	DECLARE cur_num INT;
    DECLARE name_s VARCHAR(50); 
   DECLARE id_s INT;
   DECLARE size INT;
   DECLARE last_row INT;
   DECLARE specialty CONDITION FOR SQLSTATE '45000';
   DECLARE counter INT;
   DECLARE v_finished INTEGER DEFAULT 0;         
   DEClARE my_cursor CURSOR FOR 
	 SELECT id, name FROM Dictionary;
   DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = 1;
   SET cur_num = 27;
   
   SET id_d = (SELECT id FROM dictionary WHERE name = name_d);
   SET id_w = (SELECT id FROM word WHERE name = name_w);
  START TRANSACTION;
  IF ( ISNULL(id_w) and ISNULL(id_d) )  THEN

    select 'insert into WORD';
    select 'insert into DICTIONARY';
    
    insert into word(`name`) values(name_w);
    set id_w = LAST_INSERT_ID();
    
    insert into dictionary(`name`) values(name_d);
    set id_d = LAST_INSERT_ID();
    
    insert into dictionary_word(`word_id`, `dictionary_id`) 
    	values(id_w, id_d);
    select id_w;
	select id_d;   
    
    
   END IF;
   
 IF ((id_w is not null) and ISNULL(id_d)) THEN
 
   select 'insert dictionary';
   insert into dictionary(`name`) values(name_d);
   set id_d = LAST_INSERT_ID();
    
   insert into dictionary_word(`word_id`, `dictionary_id`) 
    	values(id_w, id_d);
   select id_w;
   select id_d;   
    
 END IF;
 
  IF ((id_d is not null) and ISNULL(id_w)) THEN
   
   select 'insert word';
   insert into word(`name`) values(name_w);
   set id_w = LAST_INSERT_ID();
    
   insert into dictionary_word(`word_id`, `dictionary_id`) 
    	values(id_w, id_d);
   select id_w;
   select id_d;
 END IF;
  
  
  
 OPEN my_cursor;
  SET last_row = (SELECT FOUND_ROWS()); 
  FETCH my_cursor INTO id_s, name_s;
  set counter = 1;
  set size = length(name_s) + SizeInt(id_s);
 
  
 size: LOOP
     if counter = cur_num or last_row = counter  THEN
     	  set size = size + length(name_s) + SizeInt(id_s);
     end if;
 	 FETCH my_cursor INTO id_s, name_s;
     set counter = counter + 1;
 	IF v_finished = 1 THEN 
 		LEAVE size;
 	END IF;
 END LOOP size;


 CLOSE my_cursor;
IF last_row > cur_num THEN 
	select size;
   
ELSE 
  
  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = ' :( ';
    select MESSAGE_TEXT;

END IF;


 

  
  
    IF `_rollback` THEN
     ROLLBACK;
     end IF;

  COMMIT;
  

  
  
 END