StoredProcedure avec Filtre [Résolu]

[BenoitM]

Bonjour,

Quelle technique opteriez vous quand vous avez 2 requetes quasi identique
mais où la clause where à des paramètres en plus

Exemple:
SP1:

Code :

SELECT  * FROM Etudiant WHERE Nom=@Nom

et une autre
SP2:

Code :

SELECT  * FROM Etudiant WHERE Nom=@Nom AND Prenom=@Prenom

bon ici la requete est basique, juste pour illustré le propos mon vrai cas le select se fait sur plusieurs tables avec des jointures

Vous pensez qu'il est mieux:
-d'utiliser 2 procédures différentes
-créer un if "select ... ELSE select ...."?
-créer un string et utilisé "sp_executeSQL"
-autre?

[Jinroh77]

Est-ce que dans tous les cas vous pouvez transmettre tous les paramètres, à NULL par exemple ?

Dans ce cas vous pourriez faire :

Code :

SELECT  * FROM Etudiant WHERE Nom=@Nom AND Prenom=COALESCE(@Prenom, Prenom)

Je ne sais pas si c'est la solution optimale mais ça permet de ne conserver qu'une procédure.
Je me demande si le moteur SQL détecte l'aberration de la clause et fait en sorte de ne pas perdre de temps... ?

[elsuket]

Bonjour,

Le processeur de requêtes créera un plan de requêtes avec les paramètres de la première exécution de la procédure stockée.
En conséquence, il faut mieux écrire un IF ... ELSE.

Cependant, si vous avez de nombreux paramètres, il vaut mieux construire la chaîne de requête au fil de l'eau en testant le NULLité de la valeur des paramètres, puis soumettre la requête à l'aide de la procédure stockée sp_executesql, qui vous permettra à coup sûr d'avoir un plan par jeu de paramètres lorsque ceux-ci ne sont pas à NULL.

@++

[BenoitM]

Dans ce cas si j'ai 4 paramètres (a,b,c,d) dont 2 peuvent être null (c,d)
donc j'ai :
2 valeurs (a,b), 2 null (c,d)
3 valeurs (a,b,c), 1 null (d)
3 valeurs (a,b,d), 1 null (c)
4 valeurs (a,b,c,d)

quand j'ai la requête dans le code (.net) de mon programme j'ajoute à la requête

Code :

IF(c!=NULL) sqlwhere+="AND c=" & c

mais vu qu'ici c'est une SP et vu qu'en SQL on ne peux pas faire

Code :

AND C=NULL

mais par

Code :

C IS NULL

ca marche moins bien

[elsuket]

Il suffisait de faire ce que je vous ai suggéré :

Code :

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
CREATE PROCEDURE usp_etudiant_recherche
	@_a varchar(32)
	, @_b varchar(32)
	, @_c varchar(32) = NULL
	, @_d varchar(32) = NULL
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @sql nvarchar(1024)
 
	IF @_a IS NULL
	BEGIN
		RAISERROR('Le paramètre @_a doit être valué', 16, 1)
		RETURN
	END
 
	IF @_b IS NULL
	BEGIN
		RAISERROR('Le paramètre @_b doit être valué', 16, 1)
		RETURN
	END
 
	SET @sql = N'SELECT desColonnes FROM dbo.edutiant WHERE a = @_a AND b = @_b'
 
	SELECT @sql = CASE WHEN @_c IS NOT NULL THEN ' AND c = @_c' ELSE '' END
	SELECT @sql = CASE WHEN @_d IS NOT NULL THEN ' AND d = @_d' ELSE '' END
 
	EXEC sp_executesql
		@sql
		, N'@_a varchar(32), @_b varchar(32), @_c varchar(32), @_d vachar(32)'
		, @_a = @_a
		, @_b = @_b
		, @_c = @_c
		, @_d = @_d
END

On doit contrôler que les paramètres @_a et @_b ne sont pas NULL parce qu'on peut faire l'appel à la procédure stockée en let mettant à NULL.

mais vu qu'ici c'est une SP et vu qu'en SQL on ne peux pas faire

Code :

AND C=NULL

mais par

Code :

C IS NULL

ca marche moins bien

Oui, parce qu'en SQL, NULL n'est pas une valeur
C'est pourquoi on utilise IS et par égale

@++

[BenoitM]

Merci

juste une petite correction

Code :

1
2
SELECT @sql = @sql + CASE WHEN @_c IS NOT NULL THEN ' AND c = @_c' ELSE '' END
SELECT @sql = @sql + CASE WHEN @_d IS NOT NULL THEN ' AND d = @_d' ELSE '' END

[elsuket]

Effectivement, merci de l'avoir relevé, et ravi de vous avoir aidé

@++

[BenoitM]

Une autre façon de faire est

Code :

1
2
3
 
SELECT * FROM etudiant 
WHERE c = ISNULL(@_c, etudiant.c) OR ( etudiant.c IS NULL AND @_c IS NULL)

Ca a l'avantage d'être compilé par le compilateur

ps: en relisant le post j'ai vu que j'avais zappé le message de Jinroh77
COALESCE c'est l'équivalent du IsNull mais sans le OR ( etudiant.c is null and @_c is null) les rows qui ont c à null ne seront pas renvoyés

[elsuket]

Cela fonctionne effectivement, mais le filtre n'est pas cherchable.
En conséquence, à chaque fois que vous exécuterez la requête, elle lira toute la table.

En revanche avec la solution que je vous propose, si les colonnes c et d sont supportées par un index, la requête sera plus performante.

@++

[BenoitM]

Envoyé par elsuket

Cela fonctionne effectivement, mais le filtre n'est pas cherchable.
En conséquence, à chaque fois que vous exécuterez la requête, elle lira toute la table.

Pourquoi?

Je ne vois pas la différence

[elsuket]

Parce que SQL Server maintient des statistiques sur les valeurs qui sont dans les colonnes, pas sur les valeurs des fonctions qu'on peut appliquer à une colonne.
De la même façon un prédicat OR n'est souvent pas cherchable : il est possible de dire, à partir des statistiques, si une colonne contient une valeur et (AND) une aussi, mais pas si une colonne contient une valeur ou (OR) une autre en contient une autre

@++