Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    4

    Question Unanswered: Resolved: Recusion (Recursive) Question

    I'm using SQL Server 2000.

    My table is "tblEmployees".

    Code:
    EmployeeID | SupervisorID
    -------------------------
         1     |     NULL
         2     |      1
         3     |      1
         4     |      1
         5     |      2
         6     |      2
         7     |      2
    There are the fields "EmployeeID" and "SupervisorID" which self-joins to "EmployeeID".

    I'm trying to come up with a recursive user-defined function, view, stored procedure--whatever--to look DOWN the hiarchey. I've seen a few examples on looking up, but none looking down.

    Basically, if "EmployeeID" 1 is supervisor over 2, 3, 4--and 2 supervises 5, 6, 7, EmployeeID 1 should be considered supervisor over them also--because he supervises their supervisor.

    Thanks a bunch.
    Last edited by nullGumby; 03-30-06 at 17:53. Reason: Resolved

  2. #2
    Join Date
    Feb 2006
    Posts
    4

    Solution

    I got it--I think:

    Code:
    ALTER FUNCTION dbo.udfGetEmployeeIDsForSupervisorID (@SupervisorID int)
    	RETURNS @Return TABLE (EmployeeID int not null)
    AS
    BEGIN
    	DECLARE @EmployeeID int
    	
    	INSERT INTO @Return
    	SELECT EmployeeID
    	FROM tblEmployees
    	WHERE SupervisorID=@SupervisorID
    	
    	IF (@@ROWCOUNT > 0)
    		BEGIN
    			DECLARE tmpCursor CURSOR FOR 
    			SELECT EmployeeID
    			FROM tblEmployees
    			WHERE SupervisorID=@SupervisorID
    			
    			OPEN tmpCursor
    			
    			FETCH NEXT FROM tmpCursor INTO @EmployeeID
    			WHILE (@@FETCH_STATUS = 0)
    				BEGIN
    					INSERT INTO @Return
    					SELECT EmployeeID
    					FROM @Return
    					UNION
    					SELECT EmployeeID
    					FROM dbo.udfGetEmployeeIDsForSupervisorID(@EmployeeID)
    		
    					FETCH NEXT FROM tmpCursor INTO @EmployeeID
    				END
    
    				CLOSE tmpCursor
    				DEALLOCATE tmpCursor
    
    		END
    	RETURN
    END
    GO
    This still returns duplicates, so I have to execute it with:

    Code:
    SELECT DISTINCT * FROM dbo.udfGetEmployeeIDsForSupervisorID(1)
    There is something with SQL Server 2000 where recursive calls can only go 32 levels deep; I didn't run into that with my table, but in other circumstances that limitation might cause some problems.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Avoid recursive functions and procedures in SQL. They are inefficient, and are limited by a maximum nesting level. Below is a short article I wrote detailing what I call the "Accumulator" method of exploding recursive relationships. It is MUCH more efficient than a recursive procedure or function.
    --------------------------------------------------------------------
    The most flexible and robust method of storing hierarchical data in a database is to use a table with a recursive relationship. In this design, each record has an associated parent record ID that indicates its relative place in the hierarchy. Here is an example:

    CREATE TABLE [YourTable]
    ([RecordID] [int] IDENTITY (1, 1) NOT NULL ,
    [ParentID] [int] NULL)

    The challenge is to find a way to return all the child records and descendants for any given parent record.

    While recursion is supported within SQL Server, it is limited to 32 nested levels and it tends to be ineffecient because it does not take full advantage of SQL Server's set-based operations.

    A better algorithm is a method I call the "Accumulator Table".

    In this method, a temporary table is declared that accumulates the result set. The table is seeded with the initial key of the parent record, and then a loop is entered which inserts the immediate descendants of all the records accumulated so far which have not already been added to the table.

    Here is some skeleton code to show how it works:

    --This variable will hold the parent record ID who's children we want to find.
    declare @RecordID int
    set @RecordID = 13

    --This table will accumulate our output set.
    declare @RecordList table (RecordID int)

    --Seed the table with the @RecordID value, assuming it exists in the database.
    insert into @RecordList (RecordID)
    select RecordID
    from YourTable
    where YourTable.RecordID = @RecordID

    --Add new child records until exhausted.
    while @@RowCount > 0
    insert into @RecordList (RecordID)
    select YourTable.RecordID
    from YourTable
    inner join @RecordList RecordList on YourTable.ParentID = RecordList.RecordID
    where not exists (select * from @RecordList CurrentRecords where CurrentRecords.RecordID = YourTable.RecordID)

    --Return the result set
    select RecordID
    from @RecordList

    This method is both flexible and efficient, and the concept is adaptable to other hierarchical data challenges.

    For a completely different method of storing and manipulating hierarchical data, check out Celko's Nested Set model, which stores relationships as loops of records.

    http://www.intelligententerprise.com...stid=145525%5D
    ---------------------------------------------------------
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2006
    Posts
    4
    blindman,

    I was a little hesitant about that solution; this is being used in a multi-user environment and I was worried about table collision.

    I don't know too much about SQL Server yet, but maybe that's not an issue when you use a table in this way:

    Code:
    declare @RecordList table (RecordID int)
    That's kind of the way I'm doing it in my solution too.

    Thanks for the reply!

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will not get table collision using my method. In fact, by avoiding both cursors and recursive function calls, it will have significantly less impact on system resources.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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