Le tri dynamique en SQL

Submitted by st on

Il existe quelques méthodes qui implémentent le tri en manière dynamique dans vos SQL. Les exemples de procédures stockées sont là-dessous.

Pour ces exemples je prends la base de données démo de Microsoft

USE AdventureWorks
GO

Plusieurs IF...ELSE

La procédure contient plusieurs instructions IF...ELSE. Pas trop joli mais simple et rapide. Si vous générez le code SQL à partir du modèle ou des templates/macros cette solution vous conviendra bien. Pour le paramètre "Asc/Desc" du tri rajouterez le deuxième paramètre d'entrée et le nombre de IF...ELSE sera doublé.

CREATE PROCEDURE Person.Contact_List1
   @OrderByField nvarchar(255) 
AS
BEGIN
   IF @OrderByField = 'ContactID'
      SELECT ContactID, FirstName, LastName
      FROM Person.Contact
      ORDER BY ContactID
   ELSE IF @OrderByField = 'FirstName'
      SELECT ContactID, FirstName, LastName
      FROM Person.Contact
      ORDER BY FirstName
   ELSE IF @OrderByField = 'LastName'
      SELECT ContactID, FirstName, LastName
      FROM Person.Contact
      ORDER BY LastName
   ELSE
      SELECT ContactID, FirstName, LastName
      FROM Person.Contact
END
GO

SQL dynamique

Le requête SQL dynamique. Cette méthode est plus puissant et court. Par contre, soyez attentif. L'utilisateur doit avoir les droit de lecture sur la table. D'autre part, le risque d'attaque de type "injection SQL" sera introduit et donc il vous faudra rajouter les vérifications supplémentaires selon le type d'application.

CREATE PROCEDURE Person.Contact_List2
   @OrderByExpression nvarchar(255) 
AS
BEGIN
   DECLARE @SQL nvarchar(max)
   SELECT @SQL = 
      'SELECT ContactID, FirstName, LastName
      FROM Person.Contact
      ORDER BY ' + @OrderByExpression
   EXEC sp_executesql @SQL
END
GO

Colonnes calculées

La méthode est plus standard correspondante à SQL'92 et donc portable entre SGBD différents. Vous rajoutez les colonnes calculées en fonction du nom de champ et des options de tri "Asc/Desc".

Les colonnes calculées doivent être groupées par leur type basique ou bien être converti vers le type unique, i.e. nvarchar.

Egalement, il est possible d'implémenter plusieurs CASE...END au niveau d'instruction ORDER BY, par contre ce sera l'implémentation dépendante du SGDB.

CREATE PROCEDURE Person.Contact_List3
   @OrderByField nvarchar(255),
   @AscDesc int = 0
AS
BEGIN
   SELECT 
      ContactID, FirstName, LastName, 
      /* Groupez les columns par leur types basiques */
      CASE WHEN @AscDesc = 0 THEN
         CASE @OrderByField 
            WHEN 'ContactID' THEN ContactID
            ELSE NULL
         END
         ELSE NULL
      END AS Col1Asc,
      CASE WHEN @AscDesc = 1 THEN
         CASE @OrderByField 
            WHEN 'ContactID' THEN ContactID
            ELSE NULL
         END
         ELSE NULL
      END AS Col1Desc,
      CASE WHEN @AscDesc = 0 THEN
         CASE @OrderByField 
            WHEN 'FirstName' THEN FirstName
            WHEN 'LastName' THEN LastName
            ELSE NULL
         END
         ELSE NULL
      END AS Col2Asc,
      CASE WHEN @AscDesc = 1 THEN
         CASE @OrderByField 
            WHEN 'FirstName' THEN FirstName
            WHEN 'LastName' THEN LastName
            ELSE NULL
         END
         ELSE NULL
      END AS Col2Desc
   FROM Person.Contact
   ORDER BY Col1Asc ASC, Col1Desc DESC, Col2Asc ASC, Col2Desc DESC
END
GO

ORDER BY CASE

Cette méthode n'est pas compatible ANSI, mais elle est plus simple à comprendre et fonctionne correctement sur SQL Server.

CREATE PROCEDURE Person.Contact_List4
   @OrderByField nvarchar(255),
   @AscDesc int = 0
AS
BEGIN
   SELECT 
      ContactID, FirstName, LastName
   FROM Person.Contact
   ORDER BY       
      /* Groupez les columns par leur types basiques */
      -- Colonne entières
      CASE WHEN @AscDesc = 0 THEN
         CASE @OrderByField 
            WHEN 'ContactID' THEN ContactID
            ELSE NULL
         END
         ELSE NULL
      END Asc,
      CASE WHEN @AscDesc = 1 THEN
         CASE @OrderByField 
            WHEN 'ContactID' THEN ContactID
            ELSE NULL
         END
         ELSE NULL
      END Desc,
      -- Colonnes textes
      CASE WHEN @AscDesc = 0 THEN
         CASE @OrderByField 
            WHEN 'FirstName' THEN FirstName
            WHEN 'LastName' THEN LastName
            ELSE NULL
         END
         ELSE NULL
      END Asc,
      CASE WHEN @AscDesc = 1 THEN
         CASE @OrderByField 
            WHEN 'FirstName' THEN FirstName
            WHEN 'LastName' THEN LastName
            ELSE NULL
         END
         ELSE NULL
      END Desc
END
GO