1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90
|
CREATE PROCEDURE [batch_confor_150000_parametre] AS
BEGIN
--Initialisation des variables
DECLARE @agence as varchar(250), @nacmpt as varchar(250), @rdj as varchar(250), @montant_min as float, @devise as varchar(5), @rdj_traduc as varchar(250)
DECLARE @agence_ as varchar(250), @nacmpt_ as varchar(250), @rdj_ as varchar(250), @montant_min_ as float, @devise_ as varchar(5), @rdj_traduc_ as varchar(250)
DECLARE @sqlwhere as varchar(500), @temp as varchar(250)
--Allez sélectionner les paramètres dans confor_150000_parametre
SET @agence = ''
SET @nacmpt = ''
SET @rdj = ''
SET @montant_min = ''
SET @devise = ''
SET @sqlwhere = ''
SET @rdj_traduc = ''
Declare cursor_parametre CURSOR
For SELECT agence, nacmpt, rdj, montant_min, devise, rdj_traduc FROM confor_150000_parametre where etat = '1'
OPEN cursor_parametre
Fetch next from cursor_parametre
INTO @agence_, @nacmpt_, @rdj_, @montant_min_, @devise_, @rdj_traduc_
WHILE @@Fetch_STATUS = 0
BEGIN
SET @agence = @agence_
SET @nacmpt = @nacmpt_
SET @rdj = @rdj_
SET @montant_min = @montant_min_
SET @devise = @devise_
SET @rdj_traduc = @rdj_traduc_
Fetch next from cursor_parametre
INTO @agence_, @nacmpt_, @rdj_, @montant_min_, @devise_, @rdj_traduc_
END
CLOSE cursor_parametre;
DEALLOCATE cursor_parametre;
DECLARE @QUERY as varchar(3000)
if (@agence) <> ''
SET @sqlwhere = @sqlwhere + ' and aggest not in ('''+ replace(@agence,',',''',''') + ''')'
if (@nacmpt) <> ''
SET @sqlwhere = @sqlwhere + ' and nacmpt not in ('''+ replace(@nacmpt,',',''',''') + ''')'
if (@rdj_traduc) <> ''
BEGIN
set @sqlwhere = @sqlwhere + ' ' + @rdj_traduc
END
if (@montant_min) <> ''
BEGIN
SET @Temp = @montant_min
SET @temp = @temp + '00'
WHILE(len(@temp) < 15)
BEGIN
SET @temp = '0' + @temp
END
set @sqlwhere = @sqlwhere + ' ' + ' and substring(mtecrtt, 1, 15) >= ''' + @temp + ''''
END
if (@devise) <> ''
SET @sqlwhere = @sqlwhere + ' and cddevi = '''+ @devise + ''''
--PRINT 'select dtoper, AGGEST, nocpte, nacmpt, intcpt, substring(mtecrtt, 1, 15) as mtecrtt, substring(cddevi, 1, 3) as cddevi, sensec, lbcomp, norgdj from PRODUCTION.FRANCE.CGDBASDEX.CMECELP050 as a inner join PRODUCTION.FRANCE.CGDBASDEX.FCGCOP0S as b on a.nocpte = b.nocmpt where 1 = 1 ' + @sqlwhere
--SET @QUERY = 'select dtoper, AGGEST, nocpte, nacmpt, intcpt, substring(mtecrtt, 1, 15) as mtecrtt, substring(cddevi, 1, 3) as cddevi, sensec, lbcomp, norgdj from PRODUCTION.FRANCE.CGDBASDEX.CMECELP050 as a inner join PRODUCTION.FRANCE.CGDBASDEX.FCGCOP0S as b on a.nocpte = b.nocmpt where 1 = 1 ' + @sqlwhere
--EXEC (@QUERY)
DECLARE cursor_groupe CURSOR
FOR EXEC (@QUERY)
--On ouvre la première boucle
OPEN cursor_groupe;
--On passe en paramètre les éléments déclaré ci-dessus
FETCH NEXT FROM cursor_groupe into @temp
WHILE @@Fetch_STATUS = 0
--Début de la boucle
BEGIN
print 'cocuou'
FETCH NEXT FROM cursor_groupe into @temp
--Fin de la boucle
END
CLOSE cursor_groupe;
DEALLOCATE cursor_groupe;
END
GO |
Partager