Comment faire pour récupérer des données hiérarchiques à partir d'une Table SQL?

J'ai le dessous de procédure stockée pour retourner la liste des Id, parentId et absoluteUrls qui fonctionne très bien:

ALTER PROCEDURE [dbo].[SearchDataManager.HierarchyById] 
    @currentId AS int   
AS
BEGIN
 DECLARE @id INT 
 DECLARE @parentId INT
 DECLARE @absoluteUrl NVARCHAR(1000)

 DECLARE @Hierarchy TABLE (Id int, ParentId int, AbsoluteUrl nvarchar(1000))

 WHILE @currentId != 0
 BEGIN

     SELECT @id = Id, @parentId = ParentId, @absoluteUrl = AbsoluteUrl
     FROM dbo.[SearchDataManager.NiceUrls]
     WHERE id = @currentId

     INSERT INTO @Hierarchy  VALUES (@id, @parentId, @absoluteUrl)

    SET @currentId = @parentId
 END

    SELECT * FROM @Hierarchy  
END

La "NiceUrls" table a Id et ParentId. parentId fait référence à un enregistrement dans la même table.

il retourne comme:

 ----------------------------------
    Id  | ParentId | AbsoluteUrl
    ----------------------------------
    294 | 5        | url1
    5   | 2        | url2
    2   | 0        | url3

Le code ci-dessus fonctionne très bien en utilisant une boucle WHILE et la définition d'une variable de Table, mais je me demande, est-il une meilleure façon de récupérer les données de hiérarchie à partir d'une table?

Le problème avec le code ci-dessus est la facilité de maintenance. Si j'avais besoin de revenir à 1 plus de la colonne de la NiceUrls table, alors je serais obligé de définir une nouvelle variable, ajouter la colonne à la table en ligne, etc.

Est-il une meilleure façon de réécrire la sp?

Merci,

Quelle est la

Une expression de table commune récursive doit faire ce que vous avez besoin - msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

OriginalL'auteur The Light | 2012-06-27

  1. 19
    with Hierarchy (Id, ParentId, AbsoluteUrl, Level)
 AS
 (
    -- anchor member
     SELECT Id,
        ParentId,
        AbsoluteUrl,
        0 AS Level   
     FROM dbo.[NiceUrls]
     WHERE id = @currentId
     UNION ALL
     -- recursive members
     SELECT su.Id,
        su.ParentId,
        su.AbsoluteUrl,
        Level + 1 AS Level   
     FROM dbo.[NiceUrls] AS su
     INNER JOIN Hierarchy ON Hierarchy.ParentId = su.Id  
 )
 SELECT * FROM Hierarchy
    Nice! Et si nous avons besoin de tous les enfants de l' @currentId élément (au lieu de tous les parents), il suffit d'inverser l'état de la JOINTURE: "INNER JOIN Hiérarchie Hiérarchie.Id = su.ParentId".

    OriginalL'auteur The Light

  2. 2

    Dirait que vous voulez tous les enregistrements de la table source qui sont liées à l'identifiant d'origine.

    1) Créer un CCE qui vous donne tous les numéros d'identification (voir le lien Triple noté)

    2) participez à cette CTE de la table d'origine

    La requête est plus rapide de cette façon!

    OriginalL'auteur Christoph