DELIMITER //

CREATE PROCEDURE `TBL`(
    IN TBLNAME varchar(16), 
	IN isDuplicate int, 
	IN FLDNAME varchar(16))
BEGIN

SET GLOBAL group_concat_max_len=102400;

select 

CASE isDuplicate
			WHEN 1 THEN FLDNAME
            WHEN 0 THEN '0'
            END AS isDuplicate,
/*
CASE isDuplicate
			WHEN 1 THEN 
                TRIM(TRAILING concat(',',FLDNAME) FROM TRIM(LEADING concat(FLDNAME,',') FROM replace(tb_flds,concat(',',FLDNAME,','),',')))	
			WHEN 0 THEN '0'
		END AS tb_kv_flds,
CASE isDuplicate
			WHEN 1 THEN 
                TRIM(TRAILING concat(',\'{$',FLDNAME,'}\'') FROM TRIM(LEADING concat('\'{$',FLDNAME,'}\',') FROM replace(tb_values,concat(',\'{$',FLDNAME,'}\','),',')))
			WHEN 0 THEN '0'
		END as tb_kv_values,  
*/
CASE isDuplicate
			WHEN 1 THEN insert_kv_str
                /* replace( replace( replace(  replace( replace( replace(insert_kv_str,concat(',',FLDNAME,','),','),concat(',B.',FLDNAME,','),',') ,concat('(',FLDNAME,','),'(') , concat(',',FLDNAME,')'),')') , concat('B.',FLDNAME,','),''), concat(',B.',FLDNAME),'') */
			WHEN 0 THEN '0'
		END AS insert_kv_sql
    ,/*replace(replace(replace(tb_kv_tmp,'(,','('),',)',')'),',,',',') */ tb_kv_tmp  as tb_kv_tmptbl,
    
    CASE isDuplicate
			WHEN 1 THEN  insert_tmp_str
               /* replace( replace( replace(  replace( replace( replace(insert_str,concat(',\'{$',FLDNAME,'}\','),','),concat(',\'{$',FLDNAME,'}\''),'') ,concat('\'{$',FLDNAME,'}\','),'') , concat('(',FLDNAME,','),'(') , concat(',',FLDNAME,')'),')'), concat(',',FLDNAME,','),',') */
			WHEN 0 THEN '0'
		END as insert_tmptbl_sql,
    
    
    
    tb_flds,tb_types,tb_vars,tb_values,select_str as select_sql,insert_str as insert_sql from 

(	SELECT tb_flds,tb_types,tb_values,tb_vars
        , concat('insert into ',TBLNAME,'(',tb_flds,') select ',tb_tmp_values,' from tmp_',TBLNAME,' B where not exists (select * from ',TBLNAME,' where ',FLDNAME,'=B.',FLDNAME,')') as insert_kv_str
		, concat('select ', tb_flds, ' from ', TBLNAME) AS select_str
		, concat('insert into ', TBLNAME, '(', tb_flds, ') values(', tb_values, ')') AS insert_str
        , concat('insert into tmp_', TBLNAME, '(', tb_flds, ') values(', tb_values, ')') AS insert_tmp_str
        , CASE isDuplicate
			WHEN 1 THEN
        
        concat('DROP TEMPORARY TABLE IF EXISTS `tmp_',TBLNAME,'`;  CREATE TEMPORARY TABLE `tmp_',TBLNAME,'` (',tb_kv_tmp,')') 
           WHEN 0 THEN '0' END as tb_kv_tmp 
	FROM (
		SELECT GROUP_CONCAT(fld_name) AS tb_flds, GROUP_CONCAT(fld_type) AS tb_types
			, GROUP_CONCAT(concat('\'', '{$', fld_name, '}\'')) AS tb_values,
              GROUP_CONCAT(concat('$', fld_name)) AS tb_vars,
              GROUP_CONCAT(concat('B.',fld_name)) as tb_tmp_values
            , GROUP_CONCAT(fld_kv) AS tb_kv_tmp
		FROM (
			SELECT column_name AS fld_name, column_type AS fld_type,
            /*case when column_name=FLDNAME then '' else */
            concat(column_name,' ',column_type,' DEFAULT NULL')
            /*end */
            as fld_kv 
			
            FROM information_schema.columns
			WHERE (table_schema = 'hms_jsy'
				AND table_name = TBLNAME
				AND column_key <> 'PRI')
		) tmp_tb1
	) tmp_tb2
)tmp_tb3;


END //

DELIMITER