Delimiter //
/**
 * 
 * Utitlisé dans LOADER_CONTACTS_NORMALIZE_MAP_EMAIL_PROVIDED();
 *  #9349
 *
 */

Drop Procedure If Exists LOADER_CONTACTS_NORMALIZE_MAP_EMAIL_PROVIDED_DOINSERT//

Create Procedure LOADER_CONTACTS_NORMALIZE_MAP_EMAIL_PROVIDED_DOINSERT (
      In batchnum Int
    , In my_col Varchar(80)
    , In my_table Varchar(80)
    , In my_state Int Unsigned
)
Begin
#     NOT C.', my_col, ' REGEXP ''[[.space.][.comma.][.semicolon.]]''
    -- // New contacts //
    Set @lcedi_query_new = Concat(
            'Insert Into `', my_table, '` (
                LOADER_STATUS_ID_LOADER_STATUS, LOADER_CONTACTS_ID_LOADER_CONTACTS
                , DF_CHECK_STAT_ID_DF_CHECK_STAT
                , CONTACTS_EMAILS_EMAIL, CONTACTS_EMAILS_LAST_UPDATE
            )
            SELECT 
                C.LOADER_STATUS_ID_LOADER_STATUS, C.ID_LOADER_CONTACTS
                , ? ', -- valeur my_state
                ', IF( -- If contains a space, a comma or a semicolon, must be cleaned
                    NOT C.', my_col, ' REGEXP ''[ ,;]''
                    , C.', my_col, '
                    , LEFT(
                        C.', my_col, '
                        , LEAST(
                            IF(INSTR(C.', my_col, ', '' '') > 0, INSTR(C.', my_col, ', '' ''), LENGTH(C.', my_col, '))
                            , IF(INSTR(C.', my_col, ', '','') > 0, INSTR(C.', my_col, ', '',''), LENGTH(C.', my_col, '))
                            , IF(INSTR(C.', my_col, ', '';'') > 0, INSTR(C.', my_col, ', '';''), LENGTH(C.', my_col, '))
                        ) - 1
                    )
                )
                , Now()
            FROM
                LOADER_CONTACTS C
                INNER JOIN LOADER_STATUS S
                    ON S.ID_LOADER_STATUS = C.LOADER_STATUS_ID_LOADER_STATUS
            WHERE
                S.LOADER_STATUS_BATCH_NUM = ? ', -- valeur batchnum
                ' AND Coalesce(C.', my_col, ', '''') != '''';'
        )
    ;
-- -- AND C.ID_CONTACTS_NEW IS NOT NULL
    Set @lcedi_my_state = my_state;
    Set @lcedi_batchnum = batchnum;
    Prepare stmt_new From @lcedi_query_new;
    execute stmt_new using @lcedi_my_state, @lcedi_batchnum;	
    Deallocate prepare stmt_new;
-- 
End //
Delimiter ;