Cacher is the code snippet organizer for pro developers

INSERT INTO z01seeds VALUES
('m01oficina', 'OF[#]', 3, 0, 1, '1510', 'codigo de las oficinas'),
('m02usuario', 'USU[#]', 3, 0, 1, '15', '');

-- Definition for function sf_getnewkeyfor 

DROP FUNCTION IF EXISTS sf_getnewkeyfor$$
CREATE DEFINER = 'developer'@'%'
FUNCTION sf_getnewkeyfor(`cTablename` VARCHAR(22))
  RETURNS varchar(20) CHARSET latin1
  MODIFIES SQL DATA
BEGIN

declare nUltnumero_, nSgtenumero_, therowcount INT;
declare canyomesactual_, cUltanyomes_ varchar(4);
declare cFormatoSecu_ CHAR(20);
declare nIncre_, nLargoSecu_ tinyint;
declare cnumformtdo,ctheKey VARCHAR(20); 

set ctableName = trim(UPPER(ctableName));
set ctheKey  = '*';

-- primero busca el nombre de la tabla en la tabla de semillas
if( SELECT IF( not EXISTS(SELECT 'X' from z01seeds where upper(z01seeds.cNomTab) = ctableName), true, false)) then
	return ctheKey;
end if;

set canyomesactual_ =  CONVERT(DATE_FORMAT(curdate(),'%y%m'), char(4));

SELECT trim(upper(cFormaLlave)),
       nIncre,
       nLargoSecu,
   	 cUltAnyoMes
   	 into cFormatoSecu_,nIncre_,nLargoSecu_, cUltanyomes_  
	from  z01seeds
	where upper(z01seeds.cNomTab) = ctableName LIMIT 1;

IF locate('[#]', cFormatoSecu_)=0 then
   return ctheKey;
END IF ;

IF locate('[AA]', cFormatoSecu_)>0 then
   set cUltanyomes_ = left(cUltanyomes_,2);
	set canyomesactual_ = left(canyomesactual_,2);
END IF;

set therowcount = 0;

-- START TRANSACTION;
   WHILE ( therowcount =0 )  DO
 
	IF (locate('[AA]', cFormatoSecu_)>0 or locate('[AAMM]', cFormatoSecu_)>0 )and (cUltanyomes_='' or cUltanyomes_ is null or canyomesactual_>cUltanyomes_ ) 
	then
		UPDATE z01seeds
			set   cUltAnyoMes = canyomesactual_, nUltSecu = 0
			where upper(z01seeds.cNomTab) = ctableName LIMIT 1;
	END IF;

	SELECT nUltSecu into nUltnumero_ from  z01seeds where upper(z01seeds.cNomTab)= ctableName;
 	
   UPDATE   z01seeds
    	set      nUltSecu = nUltnumero_ + nIncre_
    	where    upper(cNomTab) = ctableName AND nUltSecu = nUltnumero_ LIMIT 1;		 
		 -- this guarantees that no one has updated it in the meantime

	 set therowcount = (SELECT ROW_COUNT());
      -- if its zero, then we need to get the next number after that and try again
END WHILE ;
-- COMMIT; 
	
-- now here, we format the number according to the pattern for this sequence 
set nSgtenumero_ =  nUltnumero_ + nIncre_ ;

IF (nLargoSecu_>0) then
      set cnumformtdo = concat( REPEAT("0", nLargoSecu_), trim(convert(nSgtenumero_, CHAR(10))) );
      set cnumformtdo = RIGHT(cnumformtdo, nLargoSecu_) ;
ELSE 
    set cnumformtdo = trim(convert(nSgtenumero_, CHAR(10)));
END IF;

set ctheKey  = cFormatoSecu_ ;
-- call sp_depura(concat_ws('','nLargoSecu_:', nLargoSecu_));

if locate('[AA]', cFormatoSecu_)>0 then
	set ctheKey = REPLACE(ctheKey ,'[AA]', canyomesactual_ ) ;
END IF;

if locate('[AAMM]', cFormatoSecu_)>0 then
	set ctheKey = REPLACE(ctheKey ,'[AAMM]', canyomesactual_ ) ; 
END IF;

set ctheKey = REPLACE(ctheKey ,'[#]', cnumformtdo);
set ctheKey = trim(ctheKey);
return ctheKey;
END
$$

-- Definition for procedure getnewkeyfor, en cuyo caso tiene parametros de salida

DROP PROCEDURE IF EXISTS getnewkeyfor$$
CREATE DEFINER = 'developer'@'%'
PROCEDURE getnewkeyfor(IN `ctableName` VARCHAR(30), OUT `ntheNuminKey` SMALLINT, OUT `ctheKey` VARCHAR(30))
  MODIFIES SQL DATA
rutina_completa:BEGIN

declare nUltnumero_, nSgtenumero_, therowcount INT;
declare canyomesactual_, cUltanyomes_ varchar(4);
declare cFormatoSecu_ CHAR(20);
declare nIncre_, nLargoSecu_ tinyint;
declare cnumformtdo VARCHAR(20); 

set ctableName = trim(UPPER(ctableName));
set nthenuminKey = -1;
set ctheKey  = '*';


-- primero busca el nombre de la tabla en la tabla de semillas
if( SELECT IF( not EXISTS(SELECT 'X' from z01seeds where upper(z01seeds.cNomTab) = ctableName), true, false)) then
	set nthenuminKey = -2;
	leave rutina_completa;
end if;

set canyomesactual_ =  CONVERT(DATE_FORMAT(curdate(),'%y%m'), char(4));

SELECT trim(upper(cFormaLlave)),
       nIncre,
       nLargoSecu,
   	 cUltAnyoMes
   	 into cFormatoSecu_,nIncre_,nLargoSecu_, cUltanyomes_  
	from  z01seeds
	where upper(z01seeds.cNomTab) = ctableName;

IF locate('[#]', cFormatoSecu_)=0 then
	set nthenuminKey = -3;
   leave rutina_completa;
END IF ;

IF locate('[AA]', cFormatoSecu_)>0 then
   set cUltanyomes_ = left(cUltanyomes_,2);
	set canyomesactual_ = left(canyomesactual_,2);
END IF;

set therowcount = 0;

START TRANSACTION;
   WHILE ( therowcount =0 )  DO
 
	IF (locate('[AA]', cFormatoSecu_)>0 or locate('[AAMM]', cFormatoSecu_)>0 )and (cUltanyomes_='' or cUltanyomes_ is null or canyomesactual_>cUltanyomes_ ) 
	then
		UPDATE z01seeds
			set   cUltAnyoMes = canyomesactual_, nUltSecu = 0
			where upper(z01seeds.cNomTab) = ctableName;
	END IF;

	SELECT nUltSecu into nUltnumero_ from  z01seeds where upper(z01seeds.cNomTab)= ctableName;
 	
   UPDATE   z01seeds
    	set      nUltSecu = nUltnumero_ + nIncre_
    	where    upper(cNomTab) = ctableName AND nUltSecu = nUltnumero_ ;		 
		 -- this guarantees that no one has updated it in the meantime

	 set therowcount = (SELECT ROW_COUNT());
      -- if its zero, then we need to get the next number after that and try again
END WHILE ;
COMMIT; 

	
-- now here, we format the number according to the pattern for this sequence 
set nSgtenumero_ =  nUltnumero_ + nIncre_ ;

IF (nLargoSecu_>0) then
      set cnumformtdo = concat( REPEAT("0", nLargoSecu_), trim(convert(nSgtenumero_, CHAR(10))) );
      set cnumformtdo = RIGHT(cnumformtdo, nLargoSecu_) ;
ELSE 
    set cnumformtdo = trim(convert(nSgtenumero_, CHAR(10)));
END IF;

set ctheKey  = cFormatoSecu_ ;
call sp_depura(concat_ws('','nLargoSecu_:', nLargoSecu_));
call sp_depura(concat_ws('','cFormatoSecu_:', cFormatoSecu_));
call sp_depura(concat_ws('','cnumformtdo:', cnumformtdo));

if locate('[AA]', cFormatoSecu_)>0 then
	set ctheKey = REPLACE(ctheKey ,'[AA]', canyomesactual_ ) ;
END IF;

if locate('[AAMM]', cFormatoSecu_)>0 then
	set ctheKey = REPLACE(ctheKey ,'[AAMM]', canyomesactual_ ) ; 
END IF;

set ctheKey = REPLACE(ctheKey ,'[#]', cnumformtdo);
set ctheKey = trim(ctheKey);
set nthenuminKey= nSgtenumero_ ;

END
$$

DROP TABLE IF EXISTS z01seeds;
CREATE TABLE z01seeds (
  cNomTab CHAR(22) NOT NULL,
  cFormaLlave CHAR(20) NOT NULL,
  nLargoSecu TINYINT(3) UNSIGNED NOT NULL,
  nUltSecu SMALLINT(5) UNSIGNED NOT NULL,
  nIncre TINYINT(3) UNSIGNED NOT NULL,
  cUltAnyoMes CHAR(4) NOT NULL,
  cDescrip VARCHAR(100) NOT NULL,
  PRIMARY KEY (cNomTab)
)
ENGINE = INNODB
AVG_ROW_LENGTH = 8192
CHARACTER SET latin1
COLLATE latin1_swedish_ci
COMMENT = 'la tabla con los datos para generar todas las claves de todas la tablas';

--- y luego la definicion de la funcion de la base de datos que me regresa una llave