Post reply

Possible recursion within sproc - what''s the best solution?

  • February 27, 2006 at 7:29 am

    #111978

    Hi,

    I have a heirachical structure using three related tables:

    Sites with SiteID as the PK and HomePageID as a FK to PageID below..

    Pages with PageID

    PageLinks with PageID and ParentID as the PK that both link back to the PageID in the Pages table.

    I want to be able to create a stored procedure that given the SiteID with list all the pages for the given site.

    I know I could probably (with a bit of help!) put together a recursive sproc based on the HomePageID, but really want to be able to have the SiteID as the starting point.

    What's the easiest way of going about this?

    Any help would be appreciated.

    Cheers,

    Julian

  • February 27, 2006 at 8:14 am

    #623470

    Hi Julian,

    There is a good example in BOL on how to expand hierarchies and show all of the children for a given parent without using recursion. You want to avoid using recursion because you'll be limited to only 32 levels of recursion.

    Have a look for "Expanding Hierarchies" in BOL or take a look at this article which is effectively the same article.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;248915

    Hope that helps,

    Karl
    source control for SQL Server

  • February 27, 2006 at 9:04 am

    #623479

    Hi Karl,

    OK, I think I get the idea - will have a play around to see what I can produce.

    Cheers,

    Julian

  • February 27, 2006 at 2:43 pm

    #623552

    I use to have UDF to return a table with 2 columns: instance name (or ID) and it's level in hierarchy.

     

    _____________
    Code for TallyGenerator

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply