Discussion :

[Cyrille36]

Bonjour,

je tente de mettre deux 'CURSORS' dans la même procédure stockée, mais cela ne fonctionne pas car le fetch_status du premier arrête le deuxieme.

Y a t'il une astuce ?

Ps : en fouillant, j'ai trouvé sys.dm_exec_cursors mais je ne parvient pas à l'utiliser.
Merci de votre aide...
Cyrille.

Exemple :

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
alter function REACH_TestCalcul2()
 returns @TCalcul TABLE 
	([Article] [nchar](15) NULL,[Substance] [int] NULL,[Poids] [real] NULL)
AS
BEGIN
 
Declare @Art nchar(15)
Declare @Sub int
Declare @typ bit
Declare @Val real
Declare @Res real
Declare @TEMPArt nchar(15)
Declare @TEMPVal real
 
begin /* Liste tous les Enregistrements de la table TEMP un par un */
   Declare C_TEMP CURSOR For 
   select Article, Valeur from REACH_Temp1
   open C_TEMP
   Fetch C_TEMP into @TEMPArt,@TEMPVal
   While (select fetch_status from sys.dm_exec_cursors(0) where name='C_TEMP') = 0
 
 
/* Insertion dans la table */
begin
   Declare C_NOMEN CURSOR For 
   select Article, Substance,typedemesure,valeur from REACH_NomenclatureSubstances
   where article = @TEMPArt
 
   select @Res=0
   open C_NOMEN
   Fetch C_NOMEN into @Art,@Sub,@Typ,@Val
   While (select fetch_status from sys.dm_exec_cursors(0) where name='C_NOMEN') = 0
   Begin
    if @Typ=1
    begin
     select @Res=@TEMPVal    
     insert into @TCalcul(article,substance,poids) values (@Art,@Sub,@Res)
    end else
    begin
     select @Res=(@Val * @TEMPVal)/100
     insert into @TCalcul(article,substance,poids) values (@Art,@Sub,@Res)
    end
    Fetch C_NOMEN into @Art,@Sub,@Typ,@Val
   end
   Close C_NOMEN
   Deallocate C_NOMEN 
end
/* FIN Insertion dans la table */
 
Fetch C_TEMP into @TEMPArt,@TEMPVal
 
end /* FIN de Liste tous les Enregistrements de la table TEMP un par un */
   Close C_TEMP
   Deallocate C_TEMP
RETURN
end

[jero44]

Bonjour,
Cette fonction retourne les informations sur tous les curseurs définis sur le serveur.
Le première précaution consiste donc à isoler les seuls curseurs relatifs à la session en utilisation la variable @@SPID

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select * from sys.dm_exec_cursors(@@spid)

Ensuite il faut compléter par une restriction sur le nom du curseur afin de retrouver son état:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select fetch_status from sys.dm_exec_cursors(@@spid) where name='ctest'

[elsuket]

Bonjour,

Votre erreur vient du fait que vous n'avez pas suffisamment indenté votre code, donc vous n'avez pas vu que vous terminez le 1er BEGIN (par END) avant d'avoir FETCHé une ligne du curseur C_TEMP.
Seule la fonction système @@FETCH_STATUS vous permet de savoir s'il reste des lignes dans votre curseur :

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
ALTER FUNCTION REACH_TestCalcul2()
	RETURNS @TCalcul TABLE 
	(
		Article NCHAR(15) NULL,
		Substance INT NULL,
		Poids REAL NULL
	)
AS
BEGIN 
	DECLARE @Art NCHAR(15)
	DECLARE @Sub INT
	DECLARE @typ BIT
	DECLARE @Val REAL
	DECLARE @Res REAL
	DECLARE @TEMPArt NCHAR(15)
	DECLARE @TEMPVal REAL
 
	-- Liste tous les Enregistrements de la table TEMP un par un */
	DECLARE C_TEMP CURSOR FOR 
		SELECT Article, Valeur
		FROM dbo.REACH_Temp1
	FOR READ ONLY
 
	OPEN C_TEMP
	FETCH NEXT FROM C_TEMP INTO @TEMPArt, @TEMPVal
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		DECLARE C_NOMEN CURSOR FOR 
			SELECT Article, Substance, typedemesure, valeur
			FROM dbo.REACH_NomenclatureSubstances
			WHERE article = @TEMPArt
		FOR READ ONLY
 
		SELECT @Res = 0
		OPEN C_NOMEN
		FETCH NEXT FROM C_NOMEN INTO @Art, @Sub, @Typ, @Val
		WHILE @@FETCH_STATUS = 0  
		BEGIN
			IF @Typ = 1
			BEGIN
				SELECT @Res = @TEMPVal
			END
			ELSE
			BEGIN
				SELECT @Res = (@Val * @TEMPVal)/100
			END
 
			INSERT INTO @TCalcul (article, substance, poids)
			VALUES (@Art, @Sub, @Res)
 
			FETCH NEXT FROM C_NOMEN INTO @Art, @Sub, @Typ, @Val
		END
		DEALLOCATE C_NOMEN 
		FETCH NEXT FROM C_TEMP INTO @TEMPArt, @TEMPVal
	END
	DEALLOCATE C_TEMP
	RETURN
END

Ensuite il y a quelques erreurs:

- à mon sens une fonction doit prendre des paramètres.
Donc votre code ne devrait pas se trouver dans une fonction mais dans une procédure stockée.

- vous utilisez des curseurs alors que vous pouvez tout à fait vous en passer.

Les curseurs sont par nature lents, et surtout anti-relationnels.
Donc ils sont gourmands en ressources, verrouillent celles-ci longtemps, et votre requête s'exécute au moins 10 fois plus lentement qu'avec une vraie requête SQL
Imaginez en plus ce que cela fait lorsque vous imbriquez des curseurs

- vous utilisez les fonctions qui sont elles aussi peu performantes. Vous avez choisi de créer une fonction qui retourne une table, et comme toute table elle est stockée, pour assurer son intégrité.
Devinez où ? Dans TempDB, la base de données système dont SQL Server se sert massivement pour de nombreuses autres taches.
Pour vous en convaincre, déployez la node "Tables", puis la node "Tables temporaires" de la base de données TempDB

Voyons maintenant ce que vous auriez pu écrire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT NS.Article,
		NS.Substance,
		CASE NS.typedemesure
			WHEN 1 THEN TMP.Valeur
			ELSE (NS.valeur * TMP.Valeur) / 100
		END AS Res
FROM dbo.REACH_NomenclatureSubstances AS NS
JOIN dbo.REACH_Temp1 AS TMP ON NS.article = TMP.article

quelques lignes, le même résultat, un temps d'exécution à coup sûr plus faible

Créons maintenant la procédure stockée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE REACH_TestCalcul
AS
BEGIN
	SELECT NS.Article,
			NS.Substance,
			CASE NS.typedemesure
				WHEN 1 THEN TMP.Valeur
				ELSE (NS.valeur * TMP.Valeur) / 100
			END AS Res
	FROM dbo.REACH_NomenclatureSubstances AS NS
	JOIN dbo.REACH_Temp1 AS TMP ON NS.article = TMP.article
END

Exécutons-la :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC dbo.REACH_TestCalcul

SVP, n'utilisez plus les curseurs sauf quand vraiment vous avez essayé toutes les possibilités mais c'est la seule qui vous reste (et ça n'en fait plus beaucoup)

Vous utilisez la fonction de gestion dynamique sys.dm_exec_cursors, ce qui montre que vous êtes au moins sous SQL Server 2005 : notez que dès cette version, il n'existe quasiment plus de raisons d'utiliser des curseurs avec l'introduction des CTE récursives
Vous ne devriez utiliser cette fonction que pour tester s'il existe encore des curseurs ouverts dans un CATCH, par exemple ...

@++

[Cyrille36]

Merci beaucoup, tout fonctionne beaucoup mieux et plus vite.

Sincères salutations, Cyrille.