De nombreuses organisations ont des hiérarchies complexes pour leurs processus métier. Construire et récupérer des données à partir de ces hiérarchies dans un système de gestion de base de données relationnelle (RDBMS) peut être une tâche fastidieuse. Cependant, SQL Server offre une meilleure solution en utilisant des expressions de table communes (CTE).

Comprendre les CTE récursives

Une CTE récursive est un type de CTE qui vous permet de récupérer des données basées sur une hiérarchie. Elle peut être expliquée en trois parties :

• Requête d’ancrage : C’est la première instruction exécutée et fournit les données de base pour la CTE.
• Séparateur : C’est la partie intermédiaire où vous pouvez utiliser des opérateurs comme UNION ALL pour combiner les résultats de la requête d’ancrage et de la requête récursive.
• Requête récursive : C’est la partie principale de la CTE où la récursion se produit. Elle fait référence à la même CTE et vous permet de construire la hiérarchie.

Exemple : Création d’un organigramme

Prenons l’exemple d’un organigramme. Nous avons une table appelée “MyDepartment” qui représente les départements et leurs relations. La hiérarchie commence par le “PDG” et se termine par le “Service des achats”. Chaque département/personne est lié à son prédécesseur en tant que nœuds.

Voici comment vous pouvez utiliser une CTE pour récupérer l’ensemble de la structure organisationnelle :

WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID, 0 AS Tree 
   FROM MyDepartment
   WHERE ParentID IS NULL
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID, OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
)
SELECT * FROM OrgTree ORDER BY Tree

Cette requête utilise la CTE nommée “OrgTree” pour récupérer la hiérarchie. La requête d’ancrage sélectionne le nœud le plus haut (PDG) avec un ParentID NULL. La requête récursive joint ensuite la table “MyDepartment” à la CTE pour construire la hiérarchie.

Le résultat de cette requête sera une table qui affiche le DepartmentID, le DepartmentName, le ParentID et le niveau de chaque département dans la hiérarchie.

Utilisation de MAXRECURSION pour éviter les boucles infinies

Lors de l’utilisation d’une CTE récursive, il est possible d’entrer dans une boucle infinie si les requêtes parent et enfant renvoient les mêmes valeurs ou des valeurs égales. Pour éviter cela, vous pouvez utiliser l’option MAXRECURSION à la fin de la commande SELECT.

Par exemple, vous pouvez définir la valeur MAXRECURSION à 10 pour limiter la profondeur de récursion :

WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID, 0 AS Tree 
   FROM MyDepartment
   WHERE ParentID IS NULL
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID, OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
)
SELECT * FROM OrgTree OPTION (MAXRECURSION 10)

Cette requête arrêtera la récursion après 10 niveaux, évitant ainsi une boucle infinie.

Conclusion

L’utilisation des expressions de table communes (CTE) dans SQL Server permet de récupérer de manière plus efficace et concise des données basées sur une hiérarchie. En comprenant la CTE récursive et en l’utilisant efficacement, vous pouvez facilement naviguer et interroger des structures organisationnelles complexes.