Bonjour à tous,

J'ai créé une procédure stockée qui permet de calculer les points gagnées entre deux relevés de points. Et je me suis rendu compte que lorsque je fais un SELECT avec des clauses WHERE, certaines ne sont pas pris en compte et notemment les paramètres d'entrées de ma procédure.
Voici le code de la procédure :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
CREATE PROCEDURE `CalculatePlayerPointsDelta` (IN season INTEGER,IN isOfficial BOOLEAN)
BEGIN
	DECLARE cSize1,cSize2,i,j INTEGER DEFAULT 0;
    	DECLARE r_numLicence, r_points,oldPoints,r_oldPoints INTEGER DEFAULT NULL;
	DECLARE r_resultDate DATE;
	DECLARE r_delta INTEGER DEFAULT NULL;
 
    	DECLARE cursor1 CURSOR FOR SELECT DISTINCT numLicence FROM `Points` p WHERE `season` = season AND `isOfficial` = isOfficial ORDER BY `numLicence`;
	DECLARE cursor2 CURSOR FOR SELECT `resultDate`,`points`,`delta` FROM `Points`p WHERE `season` = season AND `isOfficial` = isOfficial AND `numLicence` = r_numLicence ORDER BY `resultDate` ASC;
	DECLARE cursor3 CURSOR FOR SELECT `points` FROM `Points`p WHERE `isOfficial` = 1 AND `numLicence` = r_numLicence ORDER BY `resultDate` DESC; 
 
	IF isOfficial = 1 THEN
		...	
	ELSE
		OPEN cursor1;
			SET cSize1 = (SELECT FOUND_ROWS());
			SET i = 1;
 
			WHILE i <= cSize1 DO
				FETCH cursor1 INTO r_numLicence;
				OPEN cursor2;
					SET cSize2 = (SELECT FOUND_ROWS());
					SET j = 1;
					SET oldPoints  = NULL;
 
					WHILE j <= cSize2 DO
						FETCH cursor2 INTO r_resultDate,r_points,r_delta;					
						IF r_delta IS NULL THEN
							IF oldPoints IS NULL THEN
								UPDATE `Points` SET  `delta` = 0 WHERE `numLicence` = r_numLicence AND `resultDate` = r_resultDate; 										
							ELSE
								UPDATE `Points` SET  `delta` = (r_points - oldPoints) WHERE `numLicence` = r_numLicence AND `resultDate` = r_resultDate;
							END IF;
						END IF;
						SET oldPoints  = r_points;
						SET j = j+1;
					END WHILE;
				CLOSE cursor2;
				SET i= i+1;				
			END WHILE;    
		CLOSE cursor1;
	END IF;
END//
Voici la description de mes tables :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
mysql> DESC Players;DESC Seasons;DESC Points;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| numLicence   | int(11)     | NO   | PRI | NULL    | auto_increment |
| gender       | varchar(1)  | NO   |     | NULL    |                |
| nationalRank | smallint(6) | YES  |     | NULL    |                |
| rank         | smallint(6) | YES  |     | NULL    |                |
| mutation     | date        | YES  |     | NULL    |                |
| publicInfo   | tinyint(1)  | NO   |     | 1       |                |
| category     | int(11)     | NO   | MUL | 0       |                |
| season       | int(11)     | NO   | PRI | NULL    |                |
| phase        | int(11)     | NO   | PRI | NULL    |                |
| team         | int(11)     | YES  | MUL | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
 
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| libelle      | varchar(45) | NO   |     | NULL    |                |
| currentPhase | tinyint(1)  | NO   |     | 0       |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| numLicence | int(11)     | NO   | PRI | NULL    |       |
| resultDate | date        | NO   | PRI | NULL    |       |
| points     | smallint(6) | NO   |     | NULL    |       |
| delta      | smallint(6) | YES  |     | NULL    |       |
| season     | int(11)     | NO   | MUL | NULL    |       |
| isOfficial | tinyint(1)  | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Voici les résultats de l'exécution
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
mysql> CALL CalculatePlayerPointsDelta(8,0);
+------------+------------+--------+-------+--------+------------+
| numLicence | resultDate | points | delta | season | isOfficial |
+------------+------------+--------+-------+--------+------------+
|    2917470 | 2002-09-01 |   1289 |  NULL |      1 |          1 |
|    2917470 | 2003-01-01 |   1281 |  NULL |      1 |          1 |
|    2917470 | 2003-09-01 |   1365 |  NULL |      2 |          1 |
|    2917470 | 2004-01-01 |   1388 |  NULL |      2 |          1 |
|    2917470 | 2010-01-01 |   1394 |  NULL |      8 |          1 |
|    2917470 | 2010-02-13 |   1392 |  NULL |      8 |          0 |
|    2917470 | 2010-03-18 |   1398 |  NULL |      8 |          0 |
|    2917470 | 2010-04-24 |   1408 |  NULL |      8 |          0 |
|    2917470 | 2010-05-17 |   1419 |  NULL |      8 |          0 |
+------------+------------+--------+-------+--------+------------+
9 rows in set (0.00 sec)
Query OK, 0 rows affected (0.35 sec)
 
mysql> SELECT * FROM `Points`p WHERE `season` = 8 AND `isOfficial` = 0 AND `numLicence` = 2917470 ORDER BY `resultDate` ASC;
+------------+------------+--------+-------+--------+------------+
| numLicence | resultDate | points | delta | season | isOfficial |
+------------+------------+--------+-------+--------+------------+
|    2917470 | 2010-02-13 |   1392 |  NULL |      8 |          0 |
|    2917470 | 2010-03-18 |   1398 |  NULL |      8 |          0 |
|    2917470 | 2010-04-24 |   1408 |  NULL |      8 |          0 |
|    2917470 | 2010-05-17 |   1419 |  NULL |      8 |          0 |
+------------+------------+--------+-------+--------+------------+
4 rows in set (0.00 sec)
Ce que je comprend pas c'est que j'ai le bon résultat avec un copier coller du SELECT de la procédure stockée.

Je pense que ça vient de la déclaration de mes paramètres, mais je vois pas ce qui ne va pas.

Quelqu'un aurai une idée d'où cela vient ?

merci d'avance