Paramètre dynamique dans une requête

Version imprimable

30/03/2012, 10h43
labib23dz

Paramètre dynamique dans une requête

bonjour a tous.

soit la requête suivant :

Code:

Select * from table where C1=@C1 and C2=@C2 and C3=@C3

tel que :
C1,C2 et C3 sont les conditions
et
@C1,@C2 et @C3 sont des les valeurs de ces conditions; ces valeurs sont récupéré a partir de différents champs d'une form (valeur de comboBox); et il se peut aussi que l'une de ces valeurs soit a null.

exemple :
1 - si @C1 = null, alors le raquête deviendrais

Code:

Select * from table where C2=@C2 and C3=@C3

.
2 - si toute les valeurs sont a null alors la requête deviendrai

Code:

select *from table

pour résumé , a chaque fois qu'une valeur est égale a null, alors je supprime ce paramètre dans le "Where";

est il possible de faire ça dans une même requêtes?? ou sinon y'a t il un moyen de contourné ce problème ??

Merci.
30/03/2012, 11h31
Waldar
Si vous le voulez en une seule requête, il faut l'écrire ainsi :
Code:

1 2 3 4 5 SELECT * FROM MaTable WHERE (C1 = @C1 OR @C1 IS NULL) AND (C2 = @C2 OR @C2 IS NULL) AND (C3 = @C3 OR @C3 IS NULL)
Si vous voulez affiner, il faut tester les valeurs de vos paramètres et concaténer les filtres si besoin.
Vous pouvez utiliser WHERE 1=1 avant de rajouter les AND.

Bonjour,

J'aurai plutôt vu du code dynamique, parce qu'il est fort possible qu'à cause des OR, SQL Server n'ait pas d'autre choix que de lire toute la table, alors qu'un index pourrait bien supporter la requête.
En effet la compilation se fera avec les valeurs des paramètres à la première exécution, ce qui peut ne pas être optimal dans tous les cas de figure.

On peut donc écrire :
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 CREATE PROCEDURE maProcedure @C1 int , @C2 int , @C3 int AS BEGIN SET NOCOUNT ON DECLARE @sql nvarchar(max) , @params nvarchar(128) , @cr nchar(2) = nchar(13) + nchar(10) SELECT @sql = 'SELECT *' + @cr + 'FROM dbo.maTable' + @cr + 'WHERE 1 = 1' + @cr + CASE WHEN @C1 IS NULL THEN '' ELSE 'AND C1 = @C1' + @cr END + CASE WHEN @C2 IS NULL THEN '' ELSE 'AND C2 = @C2' + @cr END + CASE WHEN @C3 IS NULL THEN '' ELSE 'AND C3 = @C3' + @cr END , @params = '@C1 int, @C2 int, @C3 int' EXEC sp_executesql @sql , N'@_C1 int, @_C2 int, @_C3 int' , @_C1 = @C1 , @_C2 = @C2 , @_C3 = @C3 END
@++ ;)

Plusieurs solution existent en une seule requête sans faire de SQL dynamique...

En voici une qui nécessite de connaître le type de chaque paramètre :

Code:

1
2
3
4
5
SELECT * 
FROM   MA_TABLE 
WHERE  COALESCE(C1, '') = COALESCE(@C1, '')
  AND  COALESCE(C2, 0)  = COALESCE(@C2, 0)
  AND  COALESCE(C3, '9999-12-31') = COALESCE(@C3, '9999-12-31')

mais elle peut conduire à des erreurs si la colonne C1 contient '' ou si la colonne C2 contient 0 ou si la colonne C3 contient 9999-12-13

En voici une autre qui n'a pas ce défaut :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT * 
FROM   MA_TABLE 
WHERE  1 = CASE 
              WHEN @C1 IS NULL THEN 1
              WHEN C1 = @C1    THEN 1
              ELSE 0
       END       
  AND  1 = CASE 
              WHEN @C2 IS NULL THEN 1
              WHEN C2 = @C2    THEN 1
              ELSE 0
       END
  AND  1 = CASE 
              WHEN @C3 IS NULL THEN 1
              WHEN C3 = @C3    THEN 1
              ELSE 0
       END

Je viens même d'en faire une entrée de mon blog : http://blog.developpez.com/sqlpro/p1...where-avec-re/

A +

02/04/2012, 17h20
elsuket

Merci SQLPro.

En revanche je me demande si l'optimisation est bonne dans tous les cas, étant donné que le texte de la requête ne change pas ;)

@++ ;)

bonsoir,

merci a tous pour vos réponses et votre temps, je m'excuse aussi de n'avoir pas pu répondre un peu plus tot.

personnelement j'ai opté pour la solution de SQLpro, elle est plus facile a utiliser pour moi;cependant,j'aurai une autre question : est ce possible de rajouter une condition a la requête comme suit :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT T1.champ1,T1.champ2, T2.champ3 /*ici*/
FROM   T1,T2  /*ici*/
WHERE  1 = CASE 
              WHEN @C1 IS NULL THEN 1
              WHEN T1.C1 = @C1    THEN 1
              ELSE 0
       END       
  AND  1 = CASE 
              WHEN @C2 IS NULL THEN 1
              WHEN T1.C2 = @C2    THEN 1
              ELSE 0
       END
  AND  1 = CASE 
              WHEN @C3 IS NULL THEN 1
              WHEN T1.C3 = @C3 and T1.C3=T2.C3   THEN 1 /*ici j'ai rajouter la condition T1.C3=T2.C3*/   
              ELSE 0
       END

merci encore a vous de votre intérêt.

05/04/2012, 13h53
elsuket

A priori cela ne pose pas de problème, mais vérifiez bien que dans tous les cas vous obtenez le résultat escompté ;)

@+ ;)
09/04/2012, 12h03
labib23dz

Citation:

Envoyé par elsuket

A priori cela ne pose pas de problème, mais vérifiez bien que dans tous les cas vous obtenez le résultat escompté ;)

@+ ;)

Merci elsuket pour votre réponse, en testant la requête avec les modification que j'ai proposé, je n'obtient pas justement le résultat escompté, j'ai plutot comme résultat, le produit des deux table. y'a t il un moyen pour contourner ce problème??

merci.