De la possibilité de passer un paramètre à un CTE ?

**Christophe Charron** · 08/07/2016, 22h01

Bonsoir,
je voudrais savoir s'il est possible "d'empaqueter" dans une fonction un CTE.

Je m'explique :
mon CTE me renvoie un jeu d'enregistrement correspondant à la lecture récursive d'une gamme de fabrication.
Le père, ses fils, ses petits fils etc.

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
 
WITH Treeview_Nomenclatures_1Life (noktsoc,noktcodpf, noktcompf,noknlignom, NOCTNATCPT, noctcodecp,NOCTCOMCPT,nocnqtecom,noctunite,niveau,papa
,arctlib01_pf,arctlib02_pf
,arctlib01_psf,arctlib02_psf)
AS
(
select f1.noktsoc,f1.noktcodpf,f1.noktcompf, f1.noknlignom,f1.NOCTNATCPT,f1.noctcodecp,f1.NOCTCOMCPT,f1.nocnqtecom,f1.noctunite,0 as niveau
, f1.noktsoc+f1.noktcodpf+f1.noktcompf+cast(f1.noknlignom as varchar(6)) as papa
,f3.arctlib01,f3.arctlib02
,f4.arctlib01,f4.arctlib02
from NOMENC f1 
inner join article f3 on f1.noktsoc=f3.arktsoc and f1.NOKTCODPF=f3.arktcodart and f1.noktcompf=f3.arktcomart
inner join article f4 on f1.noktsoc=f4.arktsoc and f1.noctcodecp=f4.arktcodart and f1.NOCTCOMCPT=f4.arktcomart
where f1.noktsoc='200' and f1.NOKTCODPF='AHSASNEF1PB057' and noktcompf=''
union all
SELECT f1.noktsoc,f1.noktcodpf, f1.noktcompf,f1.noknlignom, f1.NOCTNATCPT, f1.noctcodecp,f1.NOCTCOMCPT,f1.nocnqtecom,f1.noctunite,niveau+1
,f2.papa
,f3.arctlib01,f3.arctlib02
,f4.arctlib01,f4.arctlib02
from NOMENC f1
inner join article f3 on f1.noktsoc=f3.arktsoc and f1.NOKTCODPF=f3.arktcodart and f1.noktcompf=f3.arktcomart
inner join article f4 on f1.noktsoc=f4.arktsoc and f1.noctcodecp=f4.arktcodart and f1.NOCTCOMCPT=f4.arktcomart
inner join Treeview_Nomenclatures_1Life f2 on f1.noktsoc=f2.noktsoc and f1.noktcodpf=f2.noctcodecp and f1.noktcompf=f2.NOCTCOMCPT
)
-- Requête de lecture du CTE
SELECT noktsoc,noktcodpf, noktcompf,noknlignom, NOCTNATCPT, noctcodecp,NOCTCOMCPT,nocnqtecom,noctunite,niveau,papa
,arctlib01_pf,arctlib02_pf,arctlib01_psf,arctlib02_psf
FROM Treeview_Nomenclatures_1Life

Mais je suis obligé de mettre "en dur" le code identifiant 'AHSASNEF1PB057' de mon article père.

Je voudrais lui passer en paramètre ce code identifiant.
Est-ce possible ?

D'avance, merci pour vos avis éclairés.

**hmira** · 08/07/2016, 23h41

La CTE (Common Table Expression) comme son non l'indique est une expression, c.à.d. une simple définition, un peu à l'image de la définition d'une vue non matérialisée.

A ma connaissance, on ne peut pas passer de paramètre à une CTE (même si la CTE peut très bien référencer des paramètres déclarés en amont, en dehors de la CTE !). On ne peut pas non plus passer des paramètres à une vue, que celle-ci soit matérialisée ou non.

Pour pouvoir passer des paramètres à une vue ou à une CTE, on a généralement recours à des fonctions Table, de préférence ITV, dotées de paramètres. Les fonctions de type Table viennent, combler, en quelque sorte, cette impossibilité de pourvoir paramétrer les vues.

Donc, pour revenir à ton problème, tu peux créer une fonction table de préférence ITV (Inline Table-Valued) dotée d'un paramètre nommé par exemple @IdentifiantArticlePere de type VARCHAR(8000) (type du paramètre à adapter bien sûr en fonction de la structure réelle de la colonne correspondante dbo.NOMENC.NOKTCODPF).

Soit au final le code suivant :

Code SQL :

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
CREATE FUNCTION dbo.FctStructureFabricationProduit 
(
 @IdentifiantArticlePere VARCHAR(8000)
 
)
 
RETURNS TABLE
AS
RETURN 
	WITH Treeview_Nomenclatures_1Life (noktsoc,noktcodpf, noktcompf,noknlignom, NOCTNATCPT, noctcodecp,NOCTCOMCPT,nocnqtecom,noctunite,niveau,papa
	,arctlib01_pf,arctlib02_pf
	,arctlib01_psf,arctlib02_psf)
	AS
	(
	select f1.noktsoc,f1.noktcodpf,f1.noktcompf, f1.noknlignom,f1.NOCTNATCPT,f1.noctcodecp,f1.NOCTCOMCPT,f1.nocnqtecom,f1.noctunite,0 as niveau
	, f1.noktsoc+f1.noktcodpf+f1.noktcompf+cast(f1.noknlignom as varchar(6)) as papa
	,f3.arctlib01,f3.arctlib02
	,f4.arctlib01,f4.arctlib02
	from NOMENC f1 
	inner join article f3 on f1.noktsoc=f3.arktsoc and f1.NOKTCODPF=f3.arktcodart and f1.noktcompf=f3.arktcomart
	inner join article f4 on f1.noktsoc=f4.arktsoc and f1.noctcodecp=f4.arktcodart and f1.NOCTCOMCPT=f4.arktcomart
	where f1.noktsoc='200' and f1.NOKTCODPF=@IdentifiantArticlePere and noktcompf=''
	union all
	SELECT f1.noktsoc,f1.noktcodpf, f1.noktcompf,f1.noknlignom, f1.NOCTNATCPT, f1.noctcodecp,f1.NOCTCOMCPT,f1.nocnqtecom,f1.noctunite,niveau+1
	,f2.papa
	,f3.arctlib01,f3.arctlib02
	,f4.arctlib01,f4.arctlib02
	from NOMENC f1
	inner join article f3 on f1.noktsoc=f3.arktsoc and f1.NOKTCODPF=f3.arktcodart and f1.noktcompf=f3.arktcomart
	inner join article f4 on f1.noktsoc=f4.arktsoc and f1.noctcodecp=f4.arktcodart and f1.NOCTCOMCPT=f4.arktcomart
	inner join Treeview_Nomenclatures_1Life f2 on f1.noktsoc=f2.noktsoc and f1.noktcodpf=f2.noctcodecp and f1.noktcompf=f2.NOCTCOMCPT
	)
	-- Requête de lecture du CTE
	SELECT noktsoc,noktcodpf, noktcompf,noknlignom, NOCTNATCPT, noctcodecp,NOCTCOMCPT,nocnqtecom,noctunite,niveau,papa
	,arctlib01_pf,arctlib02_pf,arctlib01_psf,arctlib02_psf
	FROM Treeview_Nomenclatures_1Life
GO

Utilisation de la fonction table

Code SQL :

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

1
2
3
SELECT SELECT noktsoc,noktcodpf, noktcompf,noknlignom, NOCTNATCPT, noctcodecp,NOCTCOMCPT,nocnqtecom,noctunite,niveau,papa
	,arctlib01_pf,arctlib02_pf,arctlib01_psf,arctlib02_psf
from dbo.FctStructureFabricationProduit	('AHSASNEF1PB057')

A+

**elsuket** · 10/07/2016, 17h59

Bonjour,

Qu'est-ce qui vous empêche de déclarer des variables ?
Vous pourriez alors créer une procédure stockée, ou si votre application est plus "familière" d'un appel à sp_executesql, passer les valeurs en paramètre.

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
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
91
92
93
94
CREATE PROCEDURE maProcedure
	@NOKTCODPF varchar(32)
AS
BEGIN
	SET NOCOUNT ON;
 
	WITH
		Treeview_Nomenclatures_1Life
		(
			noktsoc, noktcodpf, noktcompf, noknlignom, NOCTNATCPT, noctcodecp, NOCTCOMCPT, nocnqtecom, noctunite, niveau, papa
			, arctlib01_pf, arctlib02_pf, arctlib01_psf, arctlib02_psf
		)
		AS
		(
				SELECT		f1.noktsoc
						, f1.noktcodpf
						, f1.noktcompf
						, f1.noknlignom
						, f1.NOCTNATCPT
						, f1.noctcodecp
						, f1.NOCTCOMCPT
						, f1.nocnqtecom
						, f1.noctunite
						, 0 AS niveau
						, f1.noktsoc + f1.noktcodpf + f1.noktcompf + CAST(f1.noknlignom AS varchar(6)) AS papa
						, f3.arctlib01
						, f3.arctlib02
						, f4.arctlib01
						, f4.arctlib02
				FROM		NOMENC AS f1 
				INNER JOIN	article AS f3
							ON f1.noktsoc = f3.arktsoc
							AND f1.NOKTCODPF = f3.arktcodart
							AND f1.noktcompf = f3.arktcomart
				INNER JOIN	article f4
							ON f1.noktsoc = f4.arktsoc
							AND f1.noctcodecp = f4.arktcodart
							AND f1.NOCTCOMCPT = f4.arktcomart
				WHERE		f1.noktsoc = '200'
				AND		f1.NOKTCODPF = @NOKTCODPF
				AND		noktcompf = ''
			UNION ALL
				SELECT		f1.noktsoc
						, f1.noktcodpf
						, f1.noktcompf
						, f1.noknlignom
						, f1.NOCTNATCPT
						, f1.noctcodecp
						, f1.NOCTCOMCPT
						, f1.nocnqtecom
						, f1.noctunite
						, niveau + 1
						, f2.papa
						, f3.arctlib01
						, f3.arctlib02
						, f4.arctlib01
						, f4.arctlib02
				FROM		NOMENC AS f1
				INNER JOIN	article f3
							ON f1.noktsoc = f3.arktsoc
							AND f1.NOKTCODPF = f3.arktcodart
							AND f1.noktcompf = f3.arktcomart
				INNER JOIN	article f4
							ON f1.noktsoc = f4.arktsoc
							AND f1.noctcodecp = f4.arktcodart
							AND f1.NOCTCOMCPT = f4.arktcomart
				INNER JOIN	Treeview_Nomenclatures_1Life f2
							ON f1.noktsoc = f2.noktsoc
							AND f1.noktcodpf = f2.noctcodecp
							AND f1.noktcompf = f2.NOCTCOMCPT
		)
	-- Requête de lecture du CTE
	SELECT	noktsoc
		, noktcodpf
		, noktcompf
		, noknlignom
		, NOCTNATCPT
		, noctcodecp
		, NOCTCOMCPT
		, nocnqtecom
		, noctunite
		, niveau
		, papa
		, arctlib01_pf
		, arctlib02_pf
		, arctlib01_psf
		, arctlib02_psf
	FROM	Treeview_Nomenclatures_1Life;
END
 
EXEC dbo.maProcedure 'AHSASNEF1PB057'
-- Ou plus proprement
EXEC dbo.maProcedure 
	@NOKTCODPF = 'AHSASNEF1PB057'

Mais aussi :

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
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
EXEC sys.sp_executesql N'
WITH
	Treeview_Nomenclatures_1Life
	(
		noktsoc, noktcodpf, noktcompf, noknlignom, NOCTNATCPT, noctcodecp, NOCTCOMCPT, nocnqtecom, noctunite, niveau, papa
		, arctlib01_pf, arctlib02_pf, arctlib01_psf, arctlib02_psf
	)
	AS
	(
			SELECT		f1.noktsoc
					, f1.noktcodpf
					, f1.noktcompf
					, f1.noknlignom
					, f1.NOCTNATCPT
					, f1.noctcodecp
					, f1.NOCTCOMCPT
					, f1.nocnqtecom
					, f1.noctunite
					, 0 AS niveau
					, f1.noktsoc + f1.noktcodpf + f1.noktcompf + CAST(f1.noknlignom AS varchar(6)) AS papa
					, f3.arctlib01
					, f3.arctlib02
					, f4.arctlib01
					, f4.arctlib02
			FROM		NOMENC AS f1 
			INNER JOIN	article AS f3
						ON f1.noktsoc = f3.arktsoc
						AND f1.NOKTCODPF = f3.arktcodart
						AND f1.noktcompf = f3.arktcomart
			INNER JOIN	article f4
						ON f1.noktsoc = f4.arktsoc
						AND f1.noctcodecp = f4.arktcodart
						AND f1.NOCTCOMCPT = f4.arktcomart
			WHERE		f1.noktsoc = ''200''
			AND		f1.NOKTCODPF = @NOKTCODPF
			AND		noktcompf = ''''
		UNION ALL
			SELECT		f1.noktsoc
					, f1.noktcodpf
					, f1.noktcompf
					, f1.noknlignom
					, f1.NOCTNATCPT
					, f1.noctcodecp
					, f1.NOCTCOMCPT
					, f1.nocnqtecom
					, f1.noctunite
					, niveau + 1
					, f2.papa
					, f3.arctlib01
					, f3.arctlib02
					, f4.arctlib01
					, f4.arctlib02
			FROM		NOMENC AS f1
			INNER JOIN	article f3
						ON f1.noktsoc = f3.arktsoc
						AND f1.NOKTCODPF = f3.arktcodart
						AND f1.noktcompf = f3.arktcomart
			INNER JOIN	article f4
						ON f1.noktsoc = f4.arktsoc
						AND f1.noctcodecp = f4.arktcodart
						AND f1.NOCTCOMCPT = f4.arktcomart
			INNER JOIN	Treeview_Nomenclatures_1Life f2
						ON f1.noktsoc = f2.noktsoc
						AND f1.noktcodpf = f2.noctcodecp
						AND f1.noktcompf = f2.NOCTCOMCPT
	)
-- Requête de lecture du CTE
SELECT	noktsoc
	, noktcodpf
	, noktcompf
	, noknlignom
	, NOCTNATCPT
	, noctcodecp
	, NOCTCOMCPT
	, nocnqtecom
	, noctunite
	, niveau
	, papa
	, arctlib01_pf
	, arctlib02_pf
	, arctlib01_psf
	, arctlib02_psf
FROM	Treeview_Nomenclatures_1Life;'
	----
	, N'@NOKTCODPF varchar(32)'
	---
	, @NOKTCODPF = 'AHSASNEF1PB057'

@++

**Christophe Charron** · 11/07/2016, 07h26

Bonjour,
et un grand merci à tous les deux.
J'avoue me trouver un peu penaud d'avoir dérangé la communauté alors que finalement les solutions sont simples et j'aurais du les trouver par moi-même !!
Je ne sais pas laquelle des deux solutions est la plus performante , SQLment parlant, mais il me semble que la fonction table est plus ouverte car je vais pouvoir faire d'éventuelles jointures pour renvoyer d'autres informations qui manqueraient dans la fonction initiale. Fonctionnalité qui ne serait pas disponible avec une procédure stockée. Me trompe-je ?

**SQLpro** · 11/07/2016, 09h26

Mieux que le procédure, une UDF table en ligne serait parfaitement adaptée à votre besoin.... En effet dans ce cas elle est utiisable comme vue paramétrable et peut être reprise dans n'importe quelle autre requête contrairement à la procédure stockée...

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
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
CREATE FUNCTION F_T_ARBRE_GAMME(@NOKTCODPF varchar(32))
RETURNS TABLE
AS
RETURN (
	WITH
		Treeview_Nomenclatures_1Life
		(
			noktsoc, noktcodpf, noktcompf, noknlignom, NOCTNATCPT, noctcodecp, NOCTCOMCPT, nocnqtecom, noctunite, niveau, papa
			, arctlib01_pf, arctlib02_pf, arctlib01_psf, arctlib02_psf
		)
		AS
		(
				SELECT		f1.noktsoc
						, f1.noktcodpf
						, f1.noktcompf
						, f1.noknlignom
						, f1.NOCTNATCPT
						, f1.noctcodecp
						, f1.NOCTCOMCPT
						, f1.nocnqtecom
						, f1.noctunite
						, 0 AS niveau
						, f1.noktsoc + f1.noktcodpf + f1.noktcompf + CAST(f1.noknlignom AS varchar(6)) AS papa
						, f3.arctlib01
						, f3.arctlib02
						, f4.arctlib01
						, f4.arctlib02
				FROM		NOMENC AS f1 
				INNER JOIN	article AS f3
							ON f1.noktsoc = f3.arktsoc
							AND f1.NOKTCODPF = f3.arktcodart
							AND f1.noktcompf = f3.arktcomart
				INNER JOIN	article f4
							ON f1.noktsoc = f4.arktsoc
							AND f1.noctcodecp = f4.arktcodart
							AND f1.NOCTCOMCPT = f4.arktcomart
				WHERE		f1.noktsoc = '200'
				AND		f1.NOKTCODPF = @NOKTCODPF
				AND		noktcompf = ''
			UNION ALL
				SELECT		f1.noktsoc
						, f1.noktcodpf
						, f1.noktcompf
						, f1.noknlignom
						, f1.NOCTNATCPT
						, f1.noctcodecp
						, f1.NOCTCOMCPT
						, f1.nocnqtecom
						, f1.noctunite
						, niveau + 1
						, f2.papa
						, f3.arctlib01
						, f3.arctlib02
						, f4.arctlib01
						, f4.arctlib02
				FROM		NOMENC AS f1
				INNER JOIN	article f3
							ON f1.noktsoc = f3.arktsoc
							AND f1.NOKTCODPF = f3.arktcodart
							AND f1.noktcompf = f3.arktcomart
				INNER JOIN	article f4
							ON f1.noktsoc = f4.arktsoc
							AND f1.noctcodecp = f4.arktcodart
							AND f1.NOCTCOMCPT = f4.arktcomart
				INNER JOIN	Treeview_Nomenclatures_1Life f2
							ON f1.noktsoc = f2.noktsoc
							AND f1.noktcodpf = f2.noctcodecp
							AND f1.noktcompf = f2.NOCTCOMCPT
		)
	-- Requête de lecture du CTE
	SELECT	noktsoc
		, noktcodpf
		, noktcompf
		, noknlignom
		, NOCTNATCPT
		, noctcodecp
		, NOCTCOMCPT
		, nocnqtecom
		, noctunite
		, niveau
		, papa
		, arctlib01_pf
		, arctlib02_pf
		, arctlib01_psf
		, arctlib02_psf
	FROM	Treeview_Nomenclatures_1Life
);
GO

Utilisation :

Code :

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

1
2
SELECT *
FROM   F_T_ARBRE_GAMME('AHSASNEF1PB057');

Et vous pouvez donc intégrer cette fonction comme table d'une requête dans le clause from....

Pour en savoir plus sur SQL Server :
Nom : Couverture livre SQL server Eyrolles.jpg
Affichages : 347
Taille : 105,0 Ko