Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    UK
    Posts
    1

    Exclamation Unanswered: sql loop problem

    Does anyone know how to loop thru a sql server 2000 table and then run a new search according to the result from the previous search.

    Basically i have a table that holds group details. The groups all have id's and can contain mulitple child groups which also can contain multiple child groups and so on.

    I need to be able to loop thru and find all the groups according to the initial search.

    I can acheive this by returning all the groups and then using delphi to filter but as the table could potentially hold thousands of groups i would rather do this using a sql statment.

    Any information is appreciated

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    A crude method but it might work if you do not have many sublevels

    CREATE procedure sp_dfs @id int as
    declare @tempid int
    ,@tempid1 int
    ,@tempid2 int
    ,@tempid3 int
    ,@tempid4 int
    CREATE TABLE #TREES ([cursor name]id varchar(15), srno int NOT NULL IDENTITY (1, 1))
    if @id = ''
    declare [cursor name] cursor for select id from [table] where parentid is null order by id
    else
    declare [cursor name] cursor for select id from [table] where parentid = @id order by id

    open [cursor name]
    FETCH NEXT FROM [cursor name] INTO @tempid
    while @@fetch_status = 0
    begin
    insert into #TREES values (@tempid)
    declare [cursor name1] cursor for select id from [table] where parentid = @tempid order by id
    open [cursor name1]
    FETCH NEXT FROM [cursor name1] INTO @tempid1
    while @@fetch_status = 0
    begin

    insert into #TREES values (@tempid1)
    declare [cursor name2] cursor for select id from [table] where parentid = @tempid1 order by id
    open [cursor name2]
    FETCH NEXT FROM [cursor name2] INTO @tempid2
    while @@fetch_status = 0
    begin
    insert into #TREES values (@tempid2)
    declare [cursor name3] cursor for select id from [table] where parentid = @tempid2 order by id
    open [cursor name3]
    FETCH NEXT FROM [cursor name3] INTO @tempid3
    while @@fetch_status = 0
    begin
    insert into #TREES values (@tempid3)
    declare [cursor name4] cursor for select id from [table] where parentid = @tempid3 order by id
    open [cursor name4]
    FETCH NEXT FROM [cursor name4] INTO @tempid4
    while @@fetch_status = 0
    begin
    insert into #TREES values (@tempid4)
    FETCH NEXT FROM [cursor name4] INTO @tempid4
    end
    close [cursor name4]
    deallocate [cursor name4]
    FETCH NEXT FROM [cursor name3] INTO @tempid3
    end
    close [cursor name3]
    deallocate [cursor name3]
    FETCH NEXT FROM [cursor name2] INTO @tempid2
    end
    close [cursor name2]
    deallocate [cursor name2]
    FETCH NEXT FROM [cursor name1] INTO @tempid1
    end
    close [cursor name1]
    deallocate [cursor name1]
    FETCH NEXT FROM [cursor name] INTO @tempid
    end
    close [cursor name]
    deallocate [cursor name]

    set nocount off
    select a.* from [table] a,#trees b where a.id = b. order by b.srno

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a skeleton function which accepts a Branch_ID (a node value) and returns all the sub-branches.

    CREATE FUNCTION dbo.SUB_BRANCHES
    (@BRANCH_ID uniqueidentifier)
    RETURNS @BRANCH_LIST Table
    (BRANCH_ID UniqueIdentifier)
    AS
    BEGIN
    insert into @BRANCH_LIST
    select @BRANCH_ID

    while @@RowCount > 0
    begin
    insert into @BRANCH_LIST
    select TREE_TABLE.BRANCH_ID
    from TREE_TABLE
    inner join @BRANCH_LIST BRANCH_LIST on TREE_TABLE.PARENT_ID = BRANCH_LIST.BRANCH_ID
    where not exists
    (select *
    from @BRANCH_LIST CURRENT_BRANCHES
    where TREE_TABLE.BRANCH_ID = CURRENT_BRANCHES.BRANCH_ID)
    end
    Return
    END

    blindman

Posting Permissions

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