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 36 37 38 39 40 41 42 43
|
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE FindEntitesDependantesOrder
@id_entite int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Rows int
DECLARE @Level int
SET @Level = 1
DECLARE @TableEntites table(
id_entite int,
id_entite_dependante int,
level int)
INSERT INTO @TableEntites (id_entite, id_entite_dependante, level)
SELECT id_entite, id_entite_dependante, @Level
FROM am3_entites
WHERE id_entite_dependante = @id_entite
SELECT @Rows=@@ROWCOUNT
WHILE (1=1)
BEGIN
INSERT INTO @TableEntites (id_entite, id_entite_dependante, level)
SELECT am3_entites.id_entite, am3_entites.id_entite_dependante, @Level + 1
FROM am3_entites INNER JOIN @TableEntites AS te ON am3_entites.id_entite_dependante = te.id_entite
WHERE te.level = @Level
SELECT @Rows=@@ROWCOUNT
SET @Level = @Level + 1
IF @Rows = 0
BREAK;
END
SELECT id_entite, id_entite_dependante FROM @TableEntites
END
GO |
Partager