Analyse & Programmation

Ce petit article va expliquer comment faire une requête SQL récursive sous Microsoft SQL Server 2005.

Le problème

Au travail, j'avais à ajouter une sécurité sur notre fiche employée. Je devais permettre à un utilisateur de pouvoir visionner seulement les fiches de ses subordonnées et celles des subordonnées de ses subordonnées et ainsi de suite.

Je réfléchissais à comment j'allais résoudre le problème lorsque je me suis souvenu d'une requête SQL Récursive que le DBA m'avait déjà envoyée. Je l'avais regardé rapidement mais sans chercher à comprendre comment elle fonctionnait. Puisque j'avais un nouveau problème qui pouvait utiliser cette soulution, j'ai décidé de l'étudier plus en profondeur pour voir si je pouvais l'adapter.

L'idée de pouvoir sortir toute l'arbre des employés subordonnées à un autre par une simple requête SQL me plaisait, surtout si la solution était plus rapide que de programmer un algorithme pour le faire dans un langage de programmation. Je vous présente ici la solution et je vais tenter de vous expliquer comment ça fonctionne. Comme ça vous pourrez peut-être l'adapter à un de vos besoins. Les problèmes qui demande une solution recursive ne sont pas rare.

La structure BD

Dans notre fiche, la table des employés possède un attribut appelé SuperviseurId qui est une référence sur la même table des employés et indiquant son superviseur. Très typique. Voici un exemple d'enregistrements pour donner une idée.

Table employe

La solution

Maintenant, voici la requête qui retourne tous les employés subordonnés à un employé de départ.

DECLARE @SuperverseurNo int
SET @SuperviseurNo = 1;

WITH tempRecEmploye (SuperviseurNo, EmployeNo, Iter) AS
(
 SELECT SuperviseurNo, Nom, EmployeNo, 0
 FROM Employe e
 WHERE e.EmployeNo = 1
 
 UNION ALL
 
 SELECT t.EmployeNo, Nom, e.EmployeNo, t.Iter + 1
 FROM Employe e
 JOIN tempRecEmploye t ON t.EmployeNo = e.SuperviseurNo
) 

Bon, comment cela fonctionne-t-il? Ça a l'air compliqué à première vue mais ce n'est pas si pire que ça.

Commençons par la première parti qui est:

WITH tempRecEmploye (SuperviseurNo, EmployeNo, Iter) AS

C'est la partie la plus importante. Le WITH tempRecEmploye engendre une expression de table commune. Elle permet de se référencer elle-même et ainsi permet la récursivité. Elle a aussi d'autres utilités qui ne nous intéresse pas ici.

L'instruction va créer une table temporaire contenant la liste des employées avec leur superviseur.

Ensuite:

 SELECT SuperviseurNo, Nom, EmployeNo, 0
 FROM Employe e
 WHERE e.EmployeNo = 1

Le premier select entre les parenthèses est le point d'ancrage. Elle sort l'employé et son superviseur au niveau où nous voulons commencer. La racine de l'arbre. Ici, nous prenons le numéro de l'enregistrement contenant l'employé Sylvain. Il n'a pas de superviseur.

 UNION ALL

Ensuite, nous avons l'instruction UNION ALL. Cette instruction effectue une union entre le résultat de la première requête et le résultat de la deuxième.

 SELECT t.EmployeNo, Nom, e.EmployeNo, t.Iter + 1
 FROM Employee e
 JOIN tempRecEmploye t ON t.EmployeNo = e.SupervisorNo

Finalement, nous avons la requête qui démarre la récursivité.

Nous avons un SELECT sur la table employe avec une jointure sur la table temporaire. Au premier niveau, la table temporaire contient Sylvain. La requête retourne donc tout les employés dont le superviseur est Sylvain. Ensuite, la requête s'exécute à nouveau avec le résultat de la première itération de la requête récursive.

Voici ce qui se passe étape par étape:

À la première itération, la requête d'ancrage retourne le résultat suivant:

null Sylvain 1 0

le UNION ALL démarre la deuxième requête, elle sélectionne les employés dans la table employé et la jointure sort ceux qui ont comme superviseur Sylvain, le résultat est le suivant:

1 Daniel 2 1

1 Stéphane 3 1

Ensuite, puisque la table temporaire a retourné un résultat non vide, la requête récursive s'exécute à nouveau et elle retourne:

2 Monique 4 2

Ensuite, le système retourne une UNION ALL de toutes les itérations. Le ALL du UNION est obligatoire.

La colonne Iter n'est pas obligatoire. Elle utile dans le cas où vous voulez connaître le niveau hiérarchique des employés et aussi si vous voulez limiter la profondeur de la requête en ajoutant une condition à la jointure.

J'espère que ça vous sera utile.

a+

Sylvain