Procédure récursive de suppression d'arborescence

Bonjour,

Voici mon problème:

j'ai dans ma base de données une table qui crée une arborescence, je gère ce lien avec un champ idParent, qui contient null si la Famille est au sommet de l'arborescence, qui contient l'id de son supérieur dans le cas contraire.

Voici le script de création de cette table :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE Family ( id numeric IDENTITY, shortName varchar(30), fullName varchar(80), [GroupofFamily.id] numeric, [ColorofFamily.id] numeric, idParent numeric, PRIMARY KEY (id), FOREIGN KEY ([GroupofFamily.id]) REFERENCES GroupofFamily(id), FOREIGN KEY ([ColorofFamily.id]) REFERENCES ColorofFamily(id) )

---

Cette famille a un lien avec avec deux autres tables, que j'ai initialisé avec des valeurs bidons pour mes tests.

J'ai donc rempli cette table de cette manière :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

insert into Family (shortName, fullName, [GroupofFamily.id], [ColorofFamily.id], idParent) values ('1','1',1,1,null), ('1.1','1.1',1,1,1), ('1.2','1.2',1,1,1), ('1.3','1.3',1,1,1), ('1.2.1','1.2.1',1,1,3), ('1.2.2','1.2.2',1,1,3), ('1.2.3','1.2.3',1,1,3), ('2','2',1,1,null), ('1.2.3.1','1.2.3.1',1,1,7), ('1.2.3.2','1.2.3.2',1,1,7), ('1.2.3.1.1','1.2.3.1.1',1,1,9), ('1.2.3.1.2','1.2.3.1.2',1,1,9);

---

ce qui donne :

1
1.1
1.2
1.2.1
1.2.2
1.2.3
1.2.3.1
1.2.3.1.1
1.2.3.1.2
1.2.3.2
2

J'ai tenté de créer une procédure pour supprimer proprement mon arborescence, sans laisser de branche orpheline :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

CREATE PROCEDURE Family_Delete @idToDelete numeric AS BEGIN DECLARE @idBuf numeric DECLARE @idToScan numeric DECLARE id_cursor CURSOR LOCAL FOR (select id from Family where idParent = @idToDelete)   open id_cursor fetch next from id_cursor into @idToScan   while @@FETCH_STATUS = 0 begin set @idBuf = (select id from Family where idParent = @idToScan) if @idBuf is null begin set @idBuf = (select idParent from Family where id = @idToScan) delete from Family where id = @idToScan if @idBuf is not null begin execute Family_Delete @idBuf end end else begin execute Family_Delete @idToScan end   fetch next from id_cursor into @idToScan end delete from Family where id = @idToDelete   close id_cursor DEALLOCATE id_cursor END go

---

Problème :
J'essaye de supprimer la branche 1.2 :
lorsque mon arborescence dépasse le stade 1.2.3, cela m'affiche ce message d'erreur :
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Dans les autres cas je n'ai pas d'erreur et la suppression se fait sans problème.

Ce message concerne la ligne où je déclare mon curseur.
Je ne comprend pas pourquoi j'ai ce message puisque je fais appel à Family_Delete uniquement par le biais de @idToScan ou idBuf.
Or @idToScan ne contient qu'une seule valeur puisque que son contenu vient de mon curseur, et @idBuf ne peut contenir *normalement* qu'un résultat puisque la condition est "where id = ..." et que le champ id est l'identifiant de ma table (donc unique)

Je ne vois pas où est mon erreur :oops:

Voici l'état de ma table avant exécution :
http://img11.hostingpics.net/pics/826192avant.jpg

et après :
http://img11.hostingpics.net/pics/276010aprs.jpg

Bonjour,

Quelle version de SQL Server utilisez vous ?

je travaille sur SQL Server 2008,
Microsoft SQL Server Management Studio v10.0.1600.22

Après réfléxion avec un collègue, il y a un moyen qui marche sans problème et qui est beaucoup plus simple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

CREATE PROCEDURE Family_Delete @idToDelete numeric AS BEGIN DECLARE @idToScan numeric DECLARE id_cursor CURSOR LOCAL FOR select id from Family where idParent = @idToDelete   open id_cursor fetch next from id_cursor into @idToScan   while @@FETCH_STATUS = 0 begin delete from Family where id = @idToDelete execute Family_Delete @idToScan delete from Family where id = @idToScan fetch next from id_cursor into @idToScan end delete from Family where id = @idToDelete   close id_cursor DEALLOCATE id_cursor END go

---

j'avais l'impression de "couper la branche sur laquelle je suis assise" si je ne récupérais pas l'idParent de la branche que je supprimais, mais en faisant comme ça ce n'est pas un problème

Bonjour,

Depuis SQL Server 2005, on peut faire cela en une seule requête avec une expression de table commune récursive :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

CREATE PROCEDURE Family_Delete @idToDelete numeric AS BEGIN SET NOCOUNT ON   ;WITH CTE AS ( -- Recherche de la source de suppression SELECT id , idParent , 0 AS niveau FROM dbo.Family WHERE id = @idToDelete UNION ALL -- Recherche de tous les enfants par résursivité SELECT F.id , F.idParent , C.niveau + 1 FROM CTE AS C INNER JOIN dbo.Family AS F ON C.id = F.idParent ) DELETE FROM dbo.Family FROM dbo.Family AS F INNER JOIN CTE AS C ON C.id = F.id END

---

Ce sera forcément plus rapide qu'avec un curseur ;)

Vous pouvez lire l'article de SQLPro sur le sujet.
En ce qui concerne les curseurs, vous pouvez lire le billet que j'ai publié là-dessus ;)

@++ ;)

Merci beaucoup pour ces précisions ! Je débute en gestion de base de données et j'ignorais qu'on pouvait faire ça, c'est effectivement très pratique :)

Merci ;)

Notez que vous pouvez supprimer la colonne niveau de la CTE ;)

@++ ;)