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