Delimiter //
/**
	procédure stocké qui permet de vérifier si des valeurs
	sont uniques
	actepte 1 ou 2 valeurs
*/
/**	
	In tableName : le nom de la table
	In firstCheckedFieldName : le nom du champ princiaple à vérifier (obligatoire)
	In secondCheckedFieldName : le nom du champ secondaire à vérifier (clé sur 2 champ)
	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 messages de flag
    In lastStatusFieldName
    In lastStatus
*/

Drop Procedure if exists LOADER_CHECK_UNIQUE//

Create Procedure LOADER_CHECK_UNIQUE (
	In tableName varchar(70)
	, In firstCheckedFieldName varchar(70)
	, In secondCheckedFieldName varchar(70)
	, 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 @fields = Concat(' `', firstCheckedFieldName, '` c1 ');
	Set @groupClause = Concat(' `', firstCheckedFieldName, '`');
	Set @onClause = Concat(' On t1.', firstCheckedFieldName, ' = t2.c1');

	if Coalesce(secondCheckedFieldName , '') <> '' Then
		Set @fields = Concat(@fields, ', `', secondCheckedFieldName, '` c2 ');
		Set @groupClause = Concat(@groupClause, ', `', secondCheckedFieldName, '`');
		Set @onClause = Concat(@onClause, ' And t1.', secondCheckedFieldName, ' = t2.c2');
	End if;

	Set @tableName = concat('`', tableName, '`');

	Drop Temporary Table If exists t2;
	set @queryTmp = Concat('Create temporary table t2 as Select ', 
					@fields, 
					' From ', @tableName, ' ', 
					' Group By ', @groupClause, 
					' Having Count(*)>1');

-- 	select @queryTmp;
--     call cash_print(@queryTmp, 'DEBUG');

	PREPARE stmt FROM @queryTmp;
	execute stmt			
		;
	Deallocate prepare stmt;

	Set @tables = Concat(@tableName, ' t1 Inner Join t2', @onClause);
    Set @checkedFieldName = concat(firstCheckedFieldName, coalesce(concat(',', secondCheckedFieldName), ''));

--     call cash_print(@checkedFieldName, 'DEBUG');

	call LOADER_BUILD_CHECK_BASE_QUERY (@tables, @checkedFieldName,
							batchNumFieldName, batchnum, 
                            flagCountFieldName, flagMsgsFieldName, 'Valeur de champ non unique', 
                            lastStatusFieldName, lastStatus,
                            @query);

-- 	select @query;
--     call cash_print(@query, null);

	PREPARE stmt FROM @query;
	execute stmt			
		;
	Deallocate prepare stmt;

	Drop Temporary Table If exists t2;
	end ;
End //
Delimiter ;