Voici un cas de filtrage WHERE bien courant pour une requête paramétrée. Comment s’affranchir du filtre si le paramètre est NULL ? Soit l’on veut filtrer une colonne si le paramètre est valué, soit l’on veut toutes les lignes. Un bel exemple d’utilisation de COALESCE et de CASE.
Partant de la demande suivante de labib23dz :
Soit la requête suivant :
SELECT * FROM MA_TABLE WHERE C1=@C1 AND C2=@C2 AND C3=@C3
Tel que C1, C2 et C3 sont des colonnes et @C1, @C2 et @C3 sont des paramètres de filtrage pour ces colonnes dont il se peut aussi que l’une de ces valeurs soit a NULL.
Exemple 1 Рsi @C1 = null, alors la requ̻te deviendrait :
SELECT * FROM MA_TABLE WHERE C2=@C2 AND C3=@C3
Exemple 2 Рsi toute les valeurs sont a null alors la requ̻te deviendrait :
SELECT * FROM MA_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 ?
Les solutions
Solution 1 Рpremi̬re requ̻te qui n̩cessite de connątre le type de chaque param̬tre :
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.
Solution 2 – Une autre requête qui n’a pas ce défaut mais nécessite plus d’écriture :
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
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
On peut aussi écrire ça sous cette forme :
SELECT *
FROM TTT
WHERE (C1 = @C1 AND @C1 IS NOT NULL OR @C1 IS NULL)
AND (C2 = @C2 AND @C2 IS NOT NULL OR @C2 IS NULL)
AND (C3 = @C3 AND @C3 IS NOT NULL OR @C3 IS NULL)
Mais j’ai lu que les performances ne sont pas terribles sous SQL Server 2000 et qu’il valait mieux écrire la requête sous forme de sql dynamique.
http://blogs.codes-sources.com/christian/archive/2008/04/25/sql-server-pourquoi-toto-is-null-or-champ-toto-pose-probl-me-ou-l-int-r-t-du-sql-dynamique.aspx
et que par contre sous SQL Server 2005 la prise en charge de cette syntaxe serait meilleure (je ne retrouve l’article où je l’ai lu.)
Est ce que la solution 2 est équivalente en terme de performance à ma première forme ?
Je pensais que vous vouliez donner la même propriétéà NULL dans les colonnes que dans les valeurs (sorte de « valeur joker »).
Si, l’objectif est seulement de permettre colonne:NULL = valeur:NULL alors, en effet, votre écriture est correcte (et la mienne ne l’est alors pas).
Je persiste bêtement à croire que ma solution est la bonne… Démonstration :
OK (tout)
Considérer les NULL dans les colonnes n’est pas le scénario initial.
Cela dit, vous vous trompez, les clauses devraient être comme suit :
WHERE COALESCE(C1, @C1, -1) = COALESCE(@C1, C1, -1)
AND COALESCE(C2, @C2, -1) = COALESCE(@C2, C2, -1)
AND COALESCE(C3, @C3, -1) = COALESCE(@C3, C3, -1)
Mais la considération concernant la valeur à choisir (ici, -1) reste évidemment exacte.
Pour les cas de NULL dans la colonne, il suffit de rajouter un COALESCE et un paramètre dans l’autre coalesce.
Exemple :
AND COALESCE(C2, -1) = COALESCE(@C2, C2, -1) <br />
AND COALESCE(C3, -1) = COALESCE(@C3, C3, -1)
Le paramètre (ici -1) doit être choisit avec soin en regardant quel sont les valeurs impossible pour les colonnes considérées.
SQLDev a compris où était la meilleure solution :
AND C2 = COALESCE(@C2, C2) <br />
AND C3 = COALESCE(@C3, C3)
Alors effectivement, la solution 2 proposée par SQLPro ignore bien le filtre en cas de NULL, par contre la solution que j’ai proposée ci-dessus ne retourne pas les lignes lorsque la colonne est NULL.
Par exemple :
ne retourne pas les lignes qui ont NULL dans C1
J’ai déjà été confronté à ce problème, comme beaucoup d’autres j’imagine, en commençant par faire un
Puis finalement me tourner vers la forme suivante qui est très proche de la 2ème solution, avec un peu moins de ligne :
SELECT * <br />
FROM MA_TABLE <br />
WHERE C1 = CASE <br />
WHEN @C1 IS NULL THEN C1 <br />
ELSE @C1 <br />
END <br />
AND C2 = CASE <br />
WHEN @C2 IS NULL THEN C2 <br />
ELSE @C2 <br />
END <br />
AND C3 = CASE <br />
WHEN @C3 IS NULL THEN C3 <br />
ELSE @C3 <br />
END <br />
Des contraintes à utiliser cette dernière forme ?