Requêtes avec filtre paramétrés

Laisser une réponse

Dans certaines requêtes il est nécessaire de prévoir que la clause WHERE (ou HAVING) puisse être augmentées ou diminuées de différentes expressions logiques (prédicats) en fonction de certains paramètres. La plupart du temps, les développeurs vont réaliser cela en utilisant des requêtes construites dynamiquement. Ceci n’est pas toujours intéressant notamment lorsque l’on exige des performances Voici quelques techniques de contournement afin d’obtenir le même effet avec des requêtes statiques.

Plutôt que de construire dynamiquement des requêtes qui peuvent difficilement être optimisée il est possible de construire certaines expressions de requête qui rende le code SQL statique en utilisant des requêtes paramétrées. Cela possède aussi l’avantage d’empêcher toute injection de code SQL du fait même qu’il n’y a plus de SQL dynamique.

Par exemple dans une requête on peut exiger que le filtrage porte sur un nom, ou bien sur un nom + un prénom, ou bien sur un nom + un prénom + une date de naissance, ou encore toute combinaison partielle de ces éléments…

Voici notre jeu d’essais :

CREATE TABLE T (C INT);
INSERT INTO T VALUES (1);
INSERT INTO T VALUES (2);
INSERT INTO T VALUES (NULL);

1) filtrage conditionnel avec CASE

Si une colonne qui doit être filtrée sur condition est nullable, alors il faut recourir à une structure CASE :

Exemple (sous MS SQL Server) : 



DECLARE @FLAG BIT, @VAL INT; 

 

SET @FLAG = 1; 

SET @VAL = 2; 

 

SELECT * 

FROM   T 

WHERE  1 = CASE WHEN @FLAG = 1 AND C = @VAL 

                  THEN 1  

               WHEN @FLAG = 0  

                  THEN 1 

          END 

           

C 

----------- 

2

En changeant la valeur du flag on applique le filtre ou pas.

2) filtrage conditionnel avec COALESCE

Si les données à filtrer ne sont pas nullable…

UPDATE T  
SET C = 0  
WHERE C IS NULL;
 
ALTER TABLE T  
ALTER COLUMN C INT NOT NULL;

… il est possible d’utiliser la fonction COALESCE sans la paramètre « flag » en jouant sur la nullité :



DECLARE @VAL INT; 

SET @VAL = NULL; 

 

SELECT * 

FROM   T 

WHERE  C = COALESCE(@VAL, C) 

 

C 

----------- 

1 

2 

0

—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *

Laisser un commentaire