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 |
