Add new comment

Recherche avec critères dynamiques en SQL

Submitted by st on

Une fonctionnalité fréquente en développement SQL qui permet rechercher dans les données en utilisant plusieurs critères de manière libre. Afin d'avoir la solution complète y compris les méthodes de tri dynamique voir une autre article dédié à ce sujet "Le tri dynamique en SQL".

Pour chaque "patron" je vais ajouter les pictogrammes afin de vous donner les avantages et inconvénients intrinsèques.

bon plan de requête (utilise les index)
mauvais plan pour les table volumineuses (balayage d'une table)
ré-compilation permanente
ne fonctionne qu'avec les colonnes non nulles (NOT NULL)

Je prend la base de données AdventureWorks2008 pour nos tests. Notre requête doit sélectionner les personnes par leurs prénom et/ou nom.

USE AdventureWorks2008
GO
CREATE PROCEDURE Person.Person_GetByNames
   @LastName  nvarchar(50) = NULL,
   @FirstName nvarchar(50) = NULL
AS
BEGIN
  -- !! Add your code here !!
END
GO

Pour lancer les tests :

-- Filtered records only
EXEC Person.Person_GetByNames @LastName = 'Bacon', @FirstName = DEFAULT;
-- All records
EXEC Person.Person_GetByNames @LastName = DEFAULT, @FirstName = DEFAULT;

Maintenant nous allons remplacer "Add your code here" par les différentes méthodes qui implémentent la recherche avec les critères dynamiques.

SQL dynamique

Vous créez la contrainte "WHERE" dynamiquement selon des critères passés.

   DECLARE 
      @SQL nvarchar(max) = '', 
      @Params nvarchar(max) = '@LastName nvarchar(50), @FirstName nvarchar(50)';
   IF @LastName IS NOT NULL
      SET @SQL = @SQL + ' LastName = @LastName AND ';
   IF @FirstName IS NOT NULL
      SET @SQL = @SQL + ' FirstName = @FirstName AND ';
   IF @SQL <> ''
      SET @SQL = 'WHERE ' + substring(@SQL, 1, len(@SQL) - 4);
   SET @SQL = 'SELECT * FROM Person.Person ' + @SQL;
   EXEC sp_executesql @SQL, @Params, @LastName, @FirstName

Utilisation "IF ... ELSE IF ..."

   IF @LastName IS NULL AND @FirstName IS NULL
      SELECT * FROM Person.Person
   ELSE IF @LastName IS NOT NULL AND @FirstName IS NULL
      SELECT * FROM Person.Person
      WHERE LastName = @LastName
   ELSE IF @LastName IS NULL AND @FirstName IS NOT NULL
      SELECT * FROM Person.Person
      WHERE FirstName = @FirstName
   ELSE 
      SELECT * FROM Person.Person
      WHERE LastName = @LastName AND FirstName = @FirstName

Utilisation "OR"

   SELECT * FROM Person.Person
   WHERE
      (LastName = @LastName OR @LastName IS NULL) AND
      (FirstName = @FirstName OR @FirstName IS NULL)

Utilisation "CASE"

Il n'y a pas trop de différence par rapport de la méthode "OR".

   SELECT * FROM Person.Person
   WHERE 
      CASE 
         WHEN @LastName IS NULL THEN 1
         ELSE CASE WHEN LastName = @LastName THEN 1 ELSE 0 END 
      END = 1
      AND 
      CASE 
         WHEN @FirstName IS NULL THEN 1 
         ELSE CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END 
      END = 1

Utilisation coalesce()

   SELECT * FROM Person.Person
   WHERE
      LastName = coalesce(@LastName, LastName) AND
      FirstName = coalesce(@FirstName, FirstName)

Solution particulière. Pour les colonnes de type "chaine de caractères" non nulles il est possible modifier l'expression.

   SELECT * FROM Person.Person
   WHERE
      LastName LIKE coalesce(@LastName, '%') AND
      FirstName LIKE coalesce(@FirstName, '%')

Utilisation BETWEEN

Dans cette méthode il nous faudra bien choisir les valeurs constantes maximale et minimale selon le type de données.

   DECLARE 
      @MinValue nvarchar(1) = '',
      @MaxValue nvarchar(1024) = replicate(char(255), 1024);
   SELECT * FROM Person.Person
   WHERE
      LastName BETWEEN coalesce(@LastName, @MinValue) 
         AND coalesce(@LastName, @MaxValue)
      AND
      FirstName BETWEEN coalesce(@FirstName, @MinValue) 
         AND coalesce(@FirstName, @MaxValue)

Utilisation UNION ALL

Cette méthode est similaire de celle "Utilisation IF ELSE IF" mais peut être plus lourde pour l'optimisation avec la sélection de toutes les données à la fin ou en cas d'absence des index même si ce champ n'utilise pas logiquement.

   SELECT * FROM Person.Person
   WHERE
      LastName = @LastName AND @LastName IS NOT NULL
      AND
      FirstName = @FirstName AND @FirstName IS NOT NULL
   UNION ALL
   SELECT * FROM Person.Person
   WHERE
      LastName = @LastName 
      AND @LastName IS NOT NULL
      AND @FirstName IS NULL
   UNION ALL
   -- If no index on FirstName ==> may introduce a bad plan
   SELECT * FROM Person.Person
   WHERE
      FirstName = @FirstName 
      AND @FirstName IS NOT NULL 
      AND @LastName IS NULL
   -- Will always scan table
   UNION ALL
   SELECT * FROM Person.Person
   WHERE @FirstName IS NULL AND @LastName IS NULL

Solution. Pour éviter cette genre de problèmes, composez le méthode avec celle de "Utlisation IF ELSE IF"

   IF @FirstName IS NULL AND @LastName IS NULL
      SELECT * FROM Person.Person
   ELSE
      SELECT * FROM Person.Person
      WHERE
         LastName = @LastName AND @LastName IS NOT NULL
         AND
         FirstName = @FirstName AND @FirstName IS NOT NULL
      UNION ALL
      SELECT * FROM Person.Person
      WHERE
         LastName = @LastName 
         AND @LastName IS NOT NULL
         AND @FirstName IS NULL
      UNION ALL
      SELECT * FROM Person.Person
      WHERE
         FirstName = @FirstName 
         AND @FirstName IS NOT NULL 
         AND @LastName IS NULL