Logo GNT
Actualités Tests & Guides Bons Plans
GTA 6 iPhone 17 Copilot Switch 2 Temu ChatGPT Tesla
OVH Cloud OVH Cloud
Entraide Windows Windows Serveur SQL Serveur Procédure stockées pour organigramme

Procédure stockées pour organigramme

8 réponses
Avatar
Denis
Bonjour à tous,

J'ai une table stockant les données des employés.
Dans cette table, il y a , entre autre, un champs n° employé (clé) et un
champs dans lequel est stocké le n° d'employé de son supérieur hiérarchique
(lien de chaînage).

Comment, à l'aide d'une procédure stockée, générer un organigramme ?

Aloha, 2nis
Signaler un problème avec ce contenu

8 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
Philippe T [MS]
Bonjour,

En SQL 2005, il faut utiliser les CTE (Common Table Expression) en mode
récursif.


;WITH EmpCTE(empid, empname, mgrid)
AS
(
SELECT empid, empname, mgrid
FROM Employees
WHERE empid = '12345' -- Numero de l'employé
UNION ALL
SELECT E.empid, E.empname, E.mgrid
FROM Employees E
JOIN EmpCTE M ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE


Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"Denis" wrote in message
news:
Bonjour à tous,

J'ai une table stockant les données des employés.
Dans cette table, il y a , entre autre, un champs n° employé (clé) et un
champs dans lequel est stocké le n° d'employé de son supérieur
hiérarchique
(lien de chaînage).

Comment, à l'aide d'une procédure stockée, générer un organigramme ?

Aloha, 2nis


Avatar
Denis
Bonjour,

J'ai oublié de préciser que j'étais en SQL 2000 et je suppose que l'exemple
ci-dessous est spécifique à SQL 2005 ?

"Philippe T [MS]" wrote:

Bonjour,

En SQL 2005, il faut utiliser les CTE (Common Table Expression) en mode
récursif.


;WITH EmpCTE(empid, empname, mgrid)
AS
(
SELECT empid, empname, mgrid
FROM Employees
WHERE empid = '12345' -- Numero de l'employé
UNION ALL
SELECT E.empid, E.empname, E.mgrid
FROM Employees E
JOIN EmpCTE M ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE


Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"Denis" wrote in message
news:
> Bonjour à tous,
>
> J'ai une table stockant les données des employés.
> Dans cette table, il y a , entre autre, un champs n° employé (clé) et un
> champs dans lequel est stocké le n° d'employé de son supérieur
> hiérarchique
> (lien de chaînage).
>
> Comment, à l'aide d'une procédure stockée, générer un organigramme ?
>
> Aloha, 2nis





Avatar
SQLpro [MVP]
Denis a écrit :
Bonjour,

J'ai oublié de préciser que j'étais en SQL 2000 et je suppose que l'exemple
ci-dessous est spécifique à SQL 2005 ?



Oui.

Il suffit de faire une auto jointure réentrante, par exemple avec une
procédure et du sql dynamique. Sinon modélisez votre table sous forme
intervallaire, vous n'aurez alors plus de récursivité à gérer.

Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/arborescence/

A +



--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Avatar
Philippe T [MS]
Bonjour,

De mon coté, j'ai aussi une autre solution qui consiste a créer une colonne
calculée via un trigger. Cette colonne reprend la joiture Parent/Enfant et
génère une chaine du genre ParentIdSousParentIdSousSousParentId. Il est
alors possible très facilement de naviguer dans l'arbre (pour peu que vous
ayez formater l'Id a chaque niveau avec le même nombre de caractère).

Exemple

UserId ParentId Colonne
1 NULL 00001
2 1 000010002
3 1 000010003
...

Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"SQLpro [MVP]" wrote in message
news:
Denis a écrit :
Bonjour,

J'ai oublié de préciser que j'étais en SQL 2000 et je suppose que
l'exemple ci-dessous est spécifique à SQL 2005 ?



Oui.

Il suffit de faire une auto jointure réentrante, par exemple avec une
procédure et du sql dynamique. Sinon modélisez votre table sous forme
intervallaire, vous n'aurez alors plus de récursivité à gérer.

Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/arborescence/

A +



--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************


Avatar
SQLpro [MVP]
Philippe T [MS] a écrit :
Bonjour,

De mon coté, j'ai aussi une autre solution qui consiste a créer une colonne
calculée via un trigger. Cette colonne reprend la joiture Parent/Enfant et
génère une chaine du genre ParentIdSousParentIdSousSousParentId. Il est
alors possible très facilement de naviguer dans l'arbre (pour peu que vous
ayez formater l'Id a chaque niveau avec le même nombre de caractère).

Exemple

UserId ParentId Colonne
1 NULL 00001
2 1 000010002
3 1 000010003
...




Le seul hic, c'est le cout du trigger et la limitation de la donnée de
stockage du chemin en V 2000 réparée avec VARCHAR(MAX) en 2005.

mais cela ne permet pas de faire toutes les requêtes possible et
imaginable et oblige à un caractère d'échappement pour situer le noeud
dans la chaine

A +


Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"SQLpro [MVP]" wrote in message
news:
Denis a écrit :
Bonjour,

J'ai oublié de préciser que j'étais en SQL 2000 et je suppose que
l'exemple ci-dessous est spécifique à SQL 2005 ?


Oui.

Il suffit de faire une auto jointure réentrante, par exemple avec une
procédure et du sql dynamique. Sinon modélisez votre table sous forme
intervallaire, vous n'aurez alors plus de récursivité à gérer.

Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/arborescence/

A +



--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************








--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Avatar
Philippe T [MS]
Bonjour,

Entièrement d'accord. Cette méthode n'est a utiliser que si la profondeur de
l'arbre est limité (arborescence avec une profondeur limité). Par contre, le
coût du trigger peut être suportable si la quantité de mise a jour est
limité.

Par contre, la solution est très efficace en terme de sélection puisqu'il
suffit d'ajouter une clause WHERE avec un SUBSTRING de la colonne et le
nombre de caractère souhaité. Par exemple, si l'on cherche l'ensemble des
enfants du père, il suffit depuis un enfant de prendre son PATH et de
supprimer les 6 derniers caractères et d'ajouter ce PATH dans la clause
WHERE.

Personnellement, j'utilise cette méthode sur des catalogues produits de
nombreux sites Web. Le catalogue étant relativement statique, je n'ai aucun
problème a le maintenir par trigger.

Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"SQLpro [MVP]" wrote in message
news:edR$
Philippe T [MS] a écrit :
Bonjour,

De mon coté, j'ai aussi une autre solution qui consiste a créer une
colonne calculée via un trigger. Cette colonne reprend la joiture
Parent/Enfant et génère une chaine du genre
ParentIdSousParentIdSousSousParentId. Il est alors possible très
facilement de naviguer dans l'arbre (pour peu que vous ayez formater l'Id
a chaque niveau avec le même nombre de caractère).

Exemple

UserId ParentId Colonne
1 NULL 00001
2 1 000010002
3 1 000010003
...




Le seul hic, c'est le cout du trigger et la limitation de la donnée de
stockage du chemin en V 2000 réparée avec VARCHAR(MAX) en 2005.

mais cela ne permet pas de faire toutes les requêtes possible et
imaginable et oblige à un caractère d'échappement pour situer le noeud
dans la chaine

A +


Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"SQLpro [MVP]" wrote in message
news:
Denis a écrit :
Bonjour,

J'ai oublié de préciser que j'étais en SQL 2000 et je suppose que
l'exemple ci-dessous est spécifique à SQL 2005 ?


Oui.

Il suffit de faire une auto jointure réentrante, par exemple avec une
procédure et du sql dynamique. Sinon modélisez votre table sous forme
intervallaire, vous n'aurez alors plus de récursivité à gérer.

Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/arborescence/

A +



--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************








--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************


Avatar
SQLpro [MVP]
Philippe T [MS] a écrit :
Bonjour,

Entièrement d'accord. Cette méthode n'est a utiliser que si la profondeur de
l'arbre est limité (arborescence avec une profondeur limité). Par contre, le
coût du trigger peut être suportable si la quantité de mise a jour est
limité.

Par contre, la solution est très efficace en terme de sélection puisqu'il
suffit d'ajouter une clause WHERE avec un SUBSTRING de la colonne et le
nombre de caractère souhaité. Par exemple, si l'on cherche l'ensemble des
enfants du père, il suffit depuis un enfant de prendre son PATH et de
supprimer les 6 derniers caractères et d'ajouter ce PATH dans la clause
WHERE.

Personnellement, j'utilise cette méthode sur des catalogues produits de
nombreux sites Web. Le catalogue étant relativement statique, je n'ai aucun
problème a le maintenir par trigger.



J'ai fait un bench à un moment donné sur les 3 méthodes :
1) auto jointure
2) auto jointure avec copie du path
3) mode intervallaire.
en prenant comme source d'info les fichiers et répertoire d'un disque en
contenant quelques dizaine de milliers

Résultat :
1) lamentable car requête récursives
2) pas mal mais multiplie par 4 le volume des données. Donc temps de
réponse moins bon. Fragmentation énorme si modif de l'arboresence
(notamment déplacement d'un arbre)
3) mieux que 2 en select (30 à 80% de gain) et certaines requête (par
exemple calcul du niveau) près de 10 fois moins gourmandes. Mais
légèrement plus lourd à la MAJ.

Il me reste à tester cela avec WITH pour 2005, ce que je vais faire si
mon papier est accepté sur ce sujet pour la tech ed de barcelone par le
staff US !

A +




Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"SQLpro [MVP]" wrote in message
news:edR$
Philippe T [MS] a écrit :
Bonjour,

De mon coté, j'ai aussi une autre solution qui consiste a créer une
colonne calculée via un trigger. Cette colonne reprend la joiture
Parent/Enfant et génère une chaine du genre
ParentIdSousParentIdSousSousParentId. Il est alors possible très
facilement de naviguer dans l'arbre (pour peu que vous ayez formater l'Id
a chaque niveau avec le même nombre de caractère).

Exemple

UserId ParentId Colonne
1 NULL 00001
2 1 000010002
3 1 000010003
...



Le seul hic, c'est le cout du trigger et la limitation de la donnée de
stockage du chemin en V 2000 réparée avec VARCHAR(MAX) en 2005.

mais cela ne permet pas de faire toutes les requêtes possible et
imaginable et oblige à un caractère d'échappement pour situer le noeud
dans la chaine

A +

Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"SQLpro [MVP]" wrote in message
news:
Denis a écrit :
Bonjour,

J'ai oublié de préciser que j'étais en SQL 2000 et je suppose que
l'exemple ci-dessous est spécifique à SQL 2005 ?


Oui.

Il suffit de faire une auto jointure réentrante, par exemple avec une
procédure et du sql dynamique. Sinon modélisez votre table sous forme
intervallaire, vous n'aurez alors plus de récursivité à gérer.

Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/arborescence/

A +



--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************






--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************








--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Avatar
Philippe T [MS]
Bonjour,

Merci pour les infos.

Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"SQLpro [MVP]" wrote in message
news:
Philippe T [MS] a écrit :
Bonjour,

Entièrement d'accord. Cette méthode n'est a utiliser que si la profondeur
de l'arbre est limité (arborescence avec une profondeur limité). Par
contre, le coût du trigger peut être suportable si la quantité de mise a
jour est limité.

Par contre, la solution est très efficace en terme de sélection puisqu'il
suffit d'ajouter une clause WHERE avec un SUBSTRING de la colonne et le
nombre de caractère souhaité. Par exemple, si l'on cherche l'ensemble des
enfants du père, il suffit depuis un enfant de prendre son PATH et de
supprimer les 6 derniers caractères et d'ajouter ce PATH dans la clause
WHERE.

Personnellement, j'utilise cette méthode sur des catalogues produits de
nombreux sites Web. Le catalogue étant relativement statique, je n'ai
aucun problème a le maintenir par trigger.



J'ai fait un bench à un moment donné sur les 3 méthodes :
1) auto jointure
2) auto jointure avec copie du path
3) mode intervallaire.
en prenant comme source d'info les fichiers et répertoire d'un disque en
contenant quelques dizaine de milliers

Résultat :
1) lamentable car requête récursives
2) pas mal mais multiplie par 4 le volume des données. Donc temps de
réponse moins bon. Fragmentation énorme si modif de l'arboresence
(notamment déplacement d'un arbre)
3) mieux que 2 en select (30 à 80% de gain) et certaines requête (par
exemple calcul du niveau) près de 10 fois moins gourmandes. Mais
légèrement plus lourd à la MAJ.

Il me reste à tester cela avec WITH pour 2005, ce que je vais faire si mon
papier est accepté sur ce sujet pour la tech ed de barcelone par le staff
US !

A +




Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"SQLpro [MVP]" wrote in message
news:edR$
Philippe T [MS] a écrit :
Bonjour,

De mon coté, j'ai aussi une autre solution qui consiste a créer une
colonne calculée via un trigger. Cette colonne reprend la joiture
Parent/Enfant et génère une chaine du genre
ParentIdSousParentIdSousSousParentId. Il est alors possible très
facilement de naviguer dans l'arbre (pour peu que vous ayez formater
l'Id a chaque niveau avec le même nombre de caractère).

Exemple

UserId ParentId Colonne
1 NULL 00001
2 1 000010002
3 1 000010003
...



Le seul hic, c'est le cout du trigger et la limitation de la donnée de
stockage du chemin en V 2000 réparée avec VARCHAR(MAX) en 2005.

mais cela ne permet pas de faire toutes les requêtes possible et
imaginable et oblige à un caractère d'échappement pour situer le noeud
dans la chaine

A +

Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"SQLpro [MVP]" wrote in message
news:
Denis a écrit :
Bonjour,

J'ai oublié de préciser que j'étais en SQL 2000 et je suppose que
l'exemple ci-dessous est spécifique à SQL 2005 ?


Oui.

Il suffit de faire une auto jointure réentrante, par exemple avec une
procédure et du sql dynamique. Sinon modélisez votre table sous forme
intervallaire, vous n'aurez alors plus de récursivité à gérer.

Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/arborescence/

A +



--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage
SQL
Le site sur le langage SQL et les SGBDR :
http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning,
optimisation
********************* http://www.datasapiens.com
***********************






--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************








--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************