Delimiter //
/**
	procédure stoké qui vérifie la  longueur des champ

	In tableName : le nom de la table
	In checkedFieldName : le nom du champ à vérifier
	In minlength : longueur min
	In maxlength : longueur max
	In batchNumFieldName : le nom du champ pour le numéro de lot
	In batchnum : numero de lot
	In flagCountFieldName : le nom du champ totalisant les flags
	In flagMsgsFieldName : le nom du champ avec les message de flag
    In lastStatusFieldName
    In lastStatus
*/

Drop Procedure if exists LOADER_CHECK_FIELD_LENGTH//

Create Procedure LOADER_CHECK_FIELD_LENGTH (
	In tableName varchar(70)
	, In checkedFieldName varchar(70)
	, In minlength int
	, In maxlength int
	, In batchNumFieldName varchar(70)
	, In batchnum int
	, In flagCountFieldName varchar(70)
	, In flagMsgsFieldName varchar(70)
	, In lastStatusFieldName varchar(70)
	, In lastStatus varchar(150)
)
Begin
	main: begin
		
		set @minLength = coalesce(minlength, 0);
        set @maxlength = coalesce(maxlength, 0);

		call LOADER_BUILD_CHECK_BASE_QUERY (tableName, checkedFieldName,
                    batchNumFieldName, batchnum, 
                    flagCountFieldName, flagMsgsFieldName, Concat('Longueur de champ non comprise entre ',  @minLength, ' et ' , @maxlength), 
                    lastStatusFieldName, lastStatus,
                    @query
        );

		if @maxlength > 0 Then
			set @query = Concat(@query, ' And not char_length(Coalesce(`', checkedFieldName,'`, '''')) between ',  @minLength, ' and ' , @maxlength);
		Else 
			set @query = Concat(@query, ' And char_length(Coalesce(`', checkedFieldName,'`, '''')) < ',  @minLength);
		end if;

		PREPARE stmt FROM @query;
		execute stmt			
			;
		Deallocate prepare stmt;
	end ;
End //
Delimiter ;