Professional Web Applications Themes

Help with Recursive SPROC (please!) - Microsoft SQL / MS SQL Server

Hello, I am having problem writing a recursive stored procedure for a Content Management System and am hoping that someone can give me a hand with it. I have posted (below) a script to recreate a table and data to test it with, and I've included my sample stored procedure that isn't working as expected. Any help would really be appreciated, I'm stuck. I have a table which represents a hierarchical view of data. In this case, it is an example of classified ads. The hierarchy looks something like this: ................ Classifieds -----Animals ----------Dogs ---------------Golden Retrievers ---------------Poodles -----Automobiles ................ Each ...

  1. #1

    Default Help with Recursive SPROC (please!)

    Hello,

    I am having problem writing a recursive stored procedure for a Content
    Management System and am hoping that someone can give me a hand with it. I
    have posted (below) a script to recreate a table and data to test it with,
    and I've included my sample stored procedure that isn't working as expected.
    Any help would really be appreciated, I'm stuck.

    I have a table which represents a hierarchical view of data. In this case,
    it is an example of classified ads. The hierarchy looks something like this:

    ................
    Classifieds
    -----Animals
    ----------Dogs
    ---------------Golden Retrievers
    ---------------Poodles
    -----Automobiles
    ................

    Each of these above I call a NODE. Each NODE has a Parent Node, and since a
    NODE can have multiple Parent Nodes, each NODE also has a PriorParentID
    (which is its parent's parent node). (still with me here?) Each NODE
    supports many different Types of Content. What I need to do is walk the
    hierarchy from a specific NODE, up through each of it's parents to the
    top-most node. Throughout the recursion up the tree, I need to collect a
    distinct list of Content Types that the parents support.

    So, suppose that the "Classifieds" node supports "ContentTypeID" of 3. Every
    descendent node below it inherits that "ContentTypeID". So if I am looking
    at "Poodles", I want to walk the tree up through its parents, collecting the
    "ContentTypeID" all the way up, I'd end up with a "3" for a ContentTypeID
    for Poodles because one of it's parents in the hierarchy supports
    ContentTypeID "3".

    And if the "Dogs" node supports a "ContentTypeID" of 1 and "Animals"
    supports a "ContentTypeID" of 1 and 2, then I need to get back 1, 2 and 3 in
    a resultset...because as you walk up the hierarchy from Poodles to
    Classifieds, you encounter a "1" and a "2" and a "3" for ContentTypeID's.

    Here's a script to set up the data:

    -------------------------------------------------

    CREATE TABLE [tmpNodalHierarchy] (
    [ChildNodeID] [int] NULL ,
    [ParentNodeID] [int] NULL ,
    [PriorParentNodeID] [int] NULL ,
    [NodeLabel] nvarchar(50) NULL,
    [ContentTypeID] [int] NULL)
    GO

    INSERT INTO tmpNodalHierarchy
    (ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
    VALUES
    (1, 0, -1, 'Classifieds', 3)
    INSERT INTO tmpNodalHierarchy
    (ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
    VALUES
    (2, 1, 0, 'Animals', 1)
    INSERT INTO tmpNodalHierarchy
    (ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
    VALUES
    (2, 1, 0, 'Animals', 2)
    INSERT INTO tmpNodalHierarchy
    (ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
    VALUES
    (3, 1, 0, 'Automobiles', 1)
    INSERT INTO tmpNodalHierarchy
    (ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
    VALUES
    (4, 2, 1, 'Dogs', 1)
    INSERT INTO tmpNodalHierarchy
    (ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
    VALUES
    (5, 4, 2, 'Golden Retrievers', 1)
    INSERT INTO tmpNodalHierarchy
    (ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
    VALUES
    (5, 4, 2, 'Golden Retrievers', 2)
    INSERT INTO tmpNodalHierarchy
    (ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
    VALUES
    (6, 4, 2, 'Poodles', 1)
    INSERT INTO tmpNodalHierarchy
    (ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
    VALUES
    (7, 4, 2, 'Chows', NULL)
    -------------------------------------------------


    CREATE PROC dbo.tmpGetRecursiveContentTypes
    (
    ParentNodeID int,
    PriorParentNodeID int,
    ContentTypeID int = 0,
    InContentTypes nvarchar(500) = '',
    OutContentTypes nvarchar(500) = null OUTPUT
    )
    AS
    IF NOT PriorParentNodeID IS NULL
    BEGIN
    SET NOCOUNT ON
    DECLARE ContentTypes nvarchar(500)
    SELECT ContentTypes = InContentTypes
    DECLARE curContentTypeID int, tmpPriorParentNodeID int, curNodeLabel
    nvarchar(100), curChildNodeID int, curParentNodeID int,
    curPriorParentNodeID int

    WHILE 0=0
    BEGIN
    SELECT TOP 1 curContentTypeID = ContentTypeID, curParentNodeID =
    ParentNodeID, curPriorParentNodeID = PriorParentNodeID FROM
    dbo.tmpNodalHierarchy WHERE ChildNodeID=ParentNodeID and ParentNodeID =
    PriorParentNodeID and (ContentTypeID > ContentTypeID) ORDER BY
    ContentTypeID ASC
    IF ROWCOUNT = 0
    BEGIN
    SELECT curContentTypeID = ContentTypeID, curParentNodeID = ParentNodeID,
    curPriorParentNodeID = PriorParentNodeID FROM dbo.tmpNodalHierarchy WHERE
    ChildNodeID=ParentNodeID and ParentNodeID = PriorParentNodeID and
    (ContentTypeID = ContentTypeID) ORDER BY ContentTypeID ASC
    SET ContentTypeID = curContentTypeID
    BREAK
    END
    SET ContentTypeID = curContentTypeID

    IF NOT curContentTypeID IS NULL
    SELECT ContentTypes = ContentTypes + ',' + CAST(curContentTypeID as
    nvarchar(10))
    END
    EXEC dbo.tmpGetRecursiveContentTypes
    curParentNodeID,curPriorParentNodeID,ContentTyp eID,ContentTypes
    END
    SET OutContentTypes = ContentTypes
    IF NESTLEVEL=1
    SELECT ContentTypes
    GO


    --------------------------------------------------
    --To execute the sproc as if we wanted
    --to get the ContentTypeID's for "Poodles"
    --
    --
    EXEC tmpGetRecursiveContentTypes 4, 2
    --
    --------------------------------------------------


    I have been concatenating the results, but ideally I want the resultset to
    look like:

    ContentTypeIDs
    -------------------
    1
    2
    3


    Thanks for any help you can give.

    Dan


    Dan Guest

  2. #2

    Default Re: Help with Recursive SPROC (please!)

    The first problem I see is that you have to rewrite your nodes table. There
    is no need to show grandparents; it acutally confuses the situation. Write
    a table which has :

    NodeID INT ( IDENTITY 1,1),
    ParentID INT,
    NodeName VARCHAR(50) etc..

    Constrain a FK relationship from ParentID to NodeID.

    After inserting a seed row (NodeID=1, ParentID=1, NodeName=SeedRow), then
    write a trigger which restricts NodeID and ParentID from being the same
    value.

    Once this is in place, you can recurse the table to any depth building a
    node tree.

    I ( among others) put recursion examples in another thread a couple of
    months ago.. You can reference that thread at:
    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&q=%22Selecting+all+descandants+in+a+hierarchial+ relational+database%22&btnG=Google+Search&meta=

    Good luck!
    /Ty








    "Dan Caron" <com> wrote in message
    news:phx.gbl... 
    expected. 
    this: 

    Every 
    the 
    in 


    Ty Guest

  3. #3

    Default Re: Help with Recursive SPROC (please!)

    >> Each NODE has a Parent Node, and since a NODE can have multiple
    Parent Nodes, ..<<

    I think this is a typo -- by definition, a node in a hierarchy has at
    most one *parent* (superior), and zero or more *children*
    (subordinates).

    Next, the DDL is wrong; you have no contraints and no keys on the
    Hierarchy. And you need to rad ISO-11179 and stop using silly data
    element names like "ContentTypeID" instead of "content_type" instead.

    Finally, use a nested sets model (Google it -- the regulars on the
    newsgroup are sick of my "cut & paste" by now) and use a single query
    for any depth, instead of procedural code that can only recurse 32
    levels deep in SQL Server. Without any explanation, here is your tree
    structure table:

    CREATE TABLE ClassifiedHierarchy
    (node_name VARCHAR(50) NOT NULL,
    lft INTEGER NOT NULL UNIQUE,
    rgt INTEGER NOT NULL UNIQUE,
    content_type INTEGER NOT NULL
    CHECK(content_type IN (1,2,3)),
    CHECK (lft BETWEEN 1 AND rgt));
     [/ref]
    Every descendent node below it inherits that content_type. So if I am
    looking at "Poodles", I want to walk the tree up through its parents,
    collecting the content_type all the way up, I'd end up with a "3" for a
    content_type for Poodles because one of it's parents in the hierarchy
    supports content_type "3". <<

    SELECT DISTINCT ‘Poodles’, content_type
    FROM Hierarchy AS H1, Hierarchy AS H2
    WHERE H1.lft BETWEEN H2.lft AND H2.rgt
    AND H1.node_name = ‘Poodles’;

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. sproc in DW asp.net
    By Laura K in forum Dreamweaver AppDev
    Replies: 0
    Last Post: May 12th, 09:10 PM
  2. difficult sproc
    By Bobby in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 8th, 01:37 AM
  3. sproc output
    By Hassan in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 4th, 09:30 AM
  4. SPROC parameters
    By RobGT in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 24th, 03:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139