parametre contenant clause where

**nasty** · 14/09/2011, 10h58

Bonjour,

je travaille sur une procedure stockée

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
CREATE PROCEDURE Select_scans_product

	@product_barcode_product nvarchar(50) = null,
	@product_variety_name_product nvarchar(100) = null,
	@product_range_name_product nvarchar(100) = null,
	@product_comment_product nvarchar(500) = null,
	@product_is_visible_product bit = null,
	@creation_origin_product nvarchar(50) = null,
	@is_non_alimentaire_product bit = null,
	@view_1_quantite integer = null,
	@view_1_quantite_order nvarchar = null,
	@product_barcode_product_order nvarchar = null,
	@product_calories_product real = null,
	@product_glucides_product real = null,
	@product_lipides_product real = null,
	@product_protides_product real = null,
	@product_ingredient_product text = null,
	@perimetre_user nvarchar = null,
	@creation_date_product_order datetime = null,
	@quantite_scans_order nvarchar = null
AS
	DECLARE @var_perimetre_user nvarchar;
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	set @var_perimetre_user = @perimetre_user

SELECT 
product.pk_product, 
product.barcode_product, 
product.variety_name_product, 
product.range_name_product, 
product.creation_date_product, 
product.comment_product, 
product.is_visible_product, 
product.image_product, 
product.ingredients_product, 
product.calories_product, 
product.glucides_product, 
product.lipides_product,
product.protides_product, 
product.is_non_alimentaire_product, 
Family_NameFr, 
Family_NameUs, 
SubFam_NameFr, 
SubFam_NameUs, 
creation_origin_product,
View_1.quantite, 
View_quantite_scans.quantite_scans 
FROM 
product LEFT OUTER JOIN 
(SELECT 
barcode_product, 
COUNT(*) AS quantite 
FROM 
dbo.product AS product_1 
WHERE 
COALESCE (is_deleted_product, 0) = 0 
GROUP BY barcode_product) as View_1 ON product.barcode_product = View_1.barcode_product LEFT OUTER JOIN 
View_quantite_scans ON product.barcode_product = View_quantite_scans.barcode_product LEFT OUTER JOIN 
XTC_tSubFamily ON product.fk_sub_family_product = XTC_tSubFamily.SubFamilyId LEFT OUTER JOIN 
XTC_tFamily ON XTC_tSubFamily.SubFam_FamilyId = XTC_tFamily.FamilyId 
WHERE 
COALESCE(is_deleted_product,0) = 0 
AND (@product_barcode_product IS NULL OR product.barcode_product like @product_barcode_product)
AND (@product_variety_name_product IS NULL OR (LOWER(product.variety_name_product) LIKE @product_variety_name_product)
OR (@product_range_name_product IS NULL OR LOWER(product.range_name_product) LIKE @product_range_name_product)
OR (@product_comment_product IS NULL OR LOWER(product.comment_product) LIKE @product_comment_product))
AND (@product_is_visible_product IS NULL OR product.is_visible_product = @product_is_visible_product)
AND (@creation_origin_product IS NULL OR creation_origin_product = @creation_origin_product)
AND (@is_non_alimentaire_product IS NULL OR COALESCE(is_non_alimentaire_product,0) = @is_non_alimentaire_product)
AND (@product_calories_product IS NULL OR product.calories_product = @product_calories_product)
AND (@product_glucides_product IS NULL OR product.glucides_product = @product_glucides_product)
AND (@product_lipides_product IS NULL OR product.lipides_product = @product_lipides_product)
AND (@product_protides_product IS NULL OR product.protides_product = @product_protides_product)
AND (@product_ingredient_product IS NULL OR product.ingredients_product LIKE @product_ingredient_product)
AND (@var_perimetre_user IS NULL OR @var_perimetre_user)
ORDER BY CASE 
WHEN (@view_1_quantite_order IS NOT NULL) THEN View_1.quantite
WHEN (@product_barcode_product_order IS NOT NULL) THEN product.barcode_product
WHEN (@creation_date_product_order IS NOT NULL) THEN creation_date_product
WHEN (@quantite_scans_order IS NOT NULL) THEN View_quantite_scans.quantite_scans 
END DESC

END
GO

je rencontre un probleme sur la ligne en rouge, la variable @var_perimetre_user contient plusieurs valeurs qui concernent differents champs,

par exemple @var_perimetre_user peut avoir la valeur suivante

Code :

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

(creation_origin_product = 'xxxx')

ou

Code :

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

 ((range_name_product + variety_name_product LIKE N'%danone%') OR (range_name_product + variety_name_product LIKE N'%actimel%') OR ....(range_name_product + variety_name_product LIKE N'%Dany%'))

Comment je fais pour pouvoir utiliser cette variable dans ma requete?

Dans l'attente de vos reponse

Nasty

**J0r_x** · 14/09/2011, 16h39

Code :

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

1
2
3
4
5
6
 
DECLARE @Requete_SQL varchar(max)
 
SET @Requete_sql = 'Votre requête SQL'
 
Execute(@Requete_sql)

**elsuket** · 14/09/2011, 18h06

Bonjour,

En effet le conseil de J0r_x est le bon mais il aurait pu être plus détaillé et contient une erreur.
Il vous faut construire la chaîne de requête au fil de l'eau en fonction de la valeur des paramètres : s'il est NULL, vous n'ajoutez rien à la chaîne de requête, sinon vous lui ajoutez un filtre.
Ajoutez WHERE 1 = 1 à la fin de la clause FROM / JOIN et vous n'aurez plus qu'à le faire suivre par autant de AND qu'il est nécessaire.

Ensuite ce prédicat n'en est pas un :

Code :

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

AND (@var_perimetre_user IS NULL OR @var_perimetre_user)

Vous devez avoir quelque chose du style : 'OR maColonne IN (' + @var_perimetre_user + ')'

Mais cela n'aidera pas à n'avoir qu'un minimum de plans de requête pour un jeu de paramètres particulier.
Avec l'EXEC, SQL Server garde bien sûr le plan de requête en cache, mais comme il conserve les valeurs littérales sans les paramétrer automatiquement, il est fort probable que votre plan ne soit jamais réutilisé : ce sera avec le même jeu de paramètres valués avec des différentes, mais pas avec la procédure stockée système sp_executeSQL.
Il en va de même pour la liste des paramètres, que vous devez construire au fil de l'eau.

Est-ce que vous employez des caractères non-latins dans vos paramètres (c'est à dire des caractère provenant des alphabets autres que l'alphabet latin, comme le Chinois, le Japonais, le Thaï, l'Hébreu, le Cyrillique, l'Arabe, ...) ?
Parce que si ce n'est pas le cas il vaut mieux stocker vos données en varchar, ce qui vous permet de n'utiliser qu'un seul octet par caractère (ASCII), mais ne supporte que les caractères latins et les chiffres.
En nvarchar, vous utilisez Unicode, qui occupe deux octets par caractère, mais peut stocker tout caractère, et bien sûr les chiffres.

Le type text est déprécié depuis SQL Server 2005, qui l'a remplacé par varchar(max), bien plus simple à manipuler : en fait, comme toute chaîne.
ntext a été remplacé par nvarchar(max), et image par varbinary(max).
Chacun de ces types permet de stocker 2GB de données, donc 1 milliard et des poussières de caractères en nvarchar(max).

Si vous ne voyez pas comment faire, re-postez.

@++

**elsuket** · 14/09/2011, 18h41

OK, voilà (presque tout) le code :

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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
CREATE PROCEDURE Select_scans_product
	@product_barcode_product nvarchar(50) = NULL
	, @product_variety_name_product nvarchar(100) = NULL
	, @product_range_name_product nvarchar(100) = NULL
	, @product_comment_product nvarchar(500) = NULL
	, @product_is_visible_product bit = NULL
	, @creation_origin_product nvarchar(50) = NULL
	, @is_non_alimentaire_product bit = NULL
	, @view_1_quantite int = NULL
	, @view_1_quantite_order nvarchar = NULL
	, @product_barcode_product_order nvarchar = NULL
	, @product_calories_product real = NULL
	, @product_glucides_product real = NULL
	, @product_lipides_product real = NULL
	, @product_protides_product real = NULL
	, @product_ingredient_product varchar(max) = NULL
	, @perimetre_user nvarchar = NULL
	, @creation_date_product_order datetime = NULL
	, @quantite_scans_order nvarchar = NULL
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE	@var_perimetre_user nvarchar
		, @sql nvarchar(max)
		, @params nvarchar(max)
		, @session_id uniqueidentifier
 
	SELECT	@var_perimetre_user = @perimetre_user
		, @session_id = NEWSEQUENTIALID()	
 
	IF @var_perimetre_user IS NOT NULL
	BEGIN
		INSERT	INTO dbo.temp_valeurs
		(
			session_id
			, valeur
		)
		SELECT	@session_id
			, val
		FROM	dbo.split(@var_perimetre_user, ',')
	END
 
	SET @sql = N'
	SELECT		P.pk_product
			, P.barcode_product
			, P.variety_name_product
			, P.range_name_product
			, P.creation_date_product
			, P.comment_product
			, P.is_visible_product
			, P.image_product
			, P.ingredients_product
			, P.calories_product
			, P.glucides_product
			, P.lipides_product
			, P.protides_product
			, P.is_non_alimentaire_product
			, Family_NameFr
			, Family_NameUs
			, SubFam_NameFr
			, SubFam_NameUs
			, creation_origin_product
			, V.quantite
			, VQS.quantite_scans
	FROM		dbo.product AS P
	LEFT JOIN	(
				SELECT	barcode_product
					, COUNT(*) AS quantite
				FROM	dbo.product
				WHERE	COALESCE (is_deleted_product, 0) = 0
				GROUP	BY barcode_product
			) AS V
			ON P.barcode_product = V.barcode_product
	LEFT JOIN	dbo.view_quantite_scans AS VQS
				ON P.barcode_product = VQS.barcode_product
	LEFT JOIN	dbo.XTC_tSubFamily AS SF
				ON P.fk_sub_family_product = SF.SubFamilyId
	LEFT JOIN	dbo.XTC_tFamily AS F
				ON SF.SubFam_FamilyId = F.FamilyId
	WHERE		COALESCE(P.is_deleted_product,0) = 0'
 
	SELECT		@sql = @sql
			+ CASE WHEN @product_barcode_product IS NULL THEN '' ELSE ' AND P.barcode_product LIKE @product_barcode_product' END
			+ CASE WHEN @product_variety_name_product IS NULL THEN '' ELSE ' AND (LOWER(P.variety_name_product) LIKE @product_variety_name_product' END
			+ CASE WHEN @product_range_name_product IS NULL THEN '' ELSE ' AND LOWER(P.range_name_product) LIKE @product_range_name_product)' END
			+ CASE WHEN @product_comment_product IS NULL THEN '' ELSE ' AND LOWER(P.comment_product) LIKE @product_comment_product' END
			+ CASE WHEN @product_is_visible_product IS NULL THEN '' ELSE ' AND P.is_visible_product = @product_is_visible_product' END
			+ CASE WHEN @creation_origin_product IS NULL THEN '' ELSE ' AND creation_origin_product = @creation_origin_product' END
			+ CASE WHEN @is_non_alimentaire_product IS NULL THEN '' ELSE ' AND COALESCE(is_non_alimentaire_product,0) = @is_non_alimentaire_product' END
			+ CASE WHEN @product_calories_product IS NULL THEN '' ELSE ' AND P.calories_product = @product_calories_product' END	
			+ CASE WHEN @product_calories_product IS NULL THEN '' ELSE ' AND P.calories_product = @product_calories_product' END
			+ CASE WHEN @product_glucides_product IS NULL THEN '' ELSE ' AND P.glucides_product = @product_glucides_product' END
			+ CASE WHEN @product_lipides_product IS NULL THEN '' ELSE ' AND P.lipides_product = @product_lipides_product' END
			+ CASE WHEN @product_protides_product IS NULL THEN '' ELSE ' AND P.protides_product = @product_protides_product' END
			+ CASE WHEN @product_ingredient_product IS NULL THEN '' ELSE ' AND P.ingredients_product LIKE @product_ingredient_product' END
			+ CASE WHEN @var_perimetre_user IS NULL THEN '' ELSE ' OR uneColonne IN (SELECT val FROM dbo.temp_valeurs WHERE session_id = @session_id)' END
			+ ' ORDER BY '
			+ CASE
				WHEN @view_1_quantite_order IS NOT NULL THEN 'V.quantite'
				WHEN @product_barcode_product_order IS NOT NULL THEN 'P.barcode_product'
				WHEN @creation_date_product_order IS NOT NULL THEN 'creation_date_product'
				WHEN @quantite_scans_order IS NOT NULL THEN 'VQS.quantite_scans'
			END  + 'DESC'
 
	SELECT	@params  = '@product_barcode_product nvarchar(50)'
		+ ', @product_variety_name_product nvarchar(100)'
		+ ', @product_range_name_product nvarchar(100)'
		+ ', @product_comment_product nvarchar(500)'
		+ ', @product_is_visible_product bit'
		+ ', @creation_origin_product nvarchar(50)'
		+ ', @is_non_alimentaire_product bit'
		+ ', @product_calories_product real'	
		+ ', @product_glucides_product real'
		+ ', @product_lipides_product real'
		+ ', @product_protides_product real'
		+ ', @product_ingredient_product varchar(max)'
		+ ', @session_id uniqueidentifier'
 
	EXEC sp_executesql
		@sql
		, @params
		, @product_barcode_product = @product_barcode_product
		, @product_range_name_product = @product_range_name_product
		, @product_comment_product = @product_comment_product
		, @product_is_visible_product = @product_is_visible_product
		, @creation_origin_product = @creation_origin_product
		, @is_non_alimentaire_product = @is_non_alimentaire_product
		, @product_calories_product = @product_calories_product
		, @product_glucides_product = @product_glucides_product
		, @product_lipides_product = @product_lipides_product
		, @product_protides_product = @product_protides_product
		, @session_id = @session_id
 
	IF @var_perimetre_user IS NOT NULL
	BEGIN
		DELETE	FROM dbo.temp_valeurs
		WHERE	session_id = @session_id
	END
END
GO

Il vous faut créer une table pour stocker temporairement les valeurs qui constituent la chaîne @var_perimetre_user, de laquelle vous obtiendrez les valeurs distinctes par une fonction CLR de split.
Je vous conseille d'utiliser celle d'Adam Machanic qui fonctionne parfaitement.
Si ce sont des entiers, mettez la colonne au type int.
Sinon mettez le type qui convient, qui doit être varchar ou nvarchar.
Pour utiliser la fonction CLR, il vous faut passer votre base de données en "digne de confiance" :

Code :

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

1
2
ALTER DATABASE maBD
SET TRUSTWORTHY ON

Vérifiez que le propriétaire de la base de données est sa :

Code :

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

1
2
3
SELECT	owner_sid
FROM	sys.databases
WHERE	name = 'ELSUKET'

Si la valeur est différente de 0x01, exécutez :

Code :

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

EXEC sp_changedbowner 'sa'

Ensuite vous pouvez créer la fonction CLR.

Si cela ne vous tente pas (et c'est un tort), vous pouvez trouver des fonction split() en T-SQL pur facilement.

@++

**nasty** · 15/09/2011, 10h06

Ok je vous remercie pour ces reponses, je vais mettre en application

Et si ca marche, un probleme de plus resolu grace a developpez.com et a ses membres

et pour repondre a vos questions concernant les types de caracteres, le type text, je n'y peut rien, la base sur laquelle je travaille a été concu ainsi et je n'ai pas la possibilité de modifier pour rester a jour

Nasty

parametre contenant clause where

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager