Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    18

    Unanswered: Hierarchy Problem

    I need to create a stored procedure that creates a hierarchy for each employee in a large employee data table (CCINFORMATION).

    Each entry in CCINFORMATION contains, among other things, the ID number of the employee, their title, their manager's ID, and their manager's title.
    I need to use this table to build a string for each employee. That string should contain the ID of everyone in that employee's hierarchy up to the CEO, and then I need to place that string into a different table.

    I don't know Transact-SQL really well, so I am at a little bit of a loss how to set up the logic to go through each record in CCINFORMATION, and for each one build the string as I run a series of queries based on the manager's ID until I reach the CEO. Any ideas?
    -Doug Picanzi

  2. #2
    Join Date
    Aug 2003
    Posts
    18

    Functional Solution

    I managed to get this stored procedure to work, so I thought I would share, in case it helps anyone else:

    Declare @AWID varchar(50), @Title varchar(2000), @MgrAWID varchar(50), @MgrTitle varchar(2000), @hstring varchar(2000), @hnew varchar(2000), @SEARCHAWID varchar(50)

    Declare cursor1 CURSOR FOR
    Select AWID, Title, MgrAWID, MgrTitle
    FROM CCINFORMATION

    OPEN cursor1
    FETCH NEXT FROM cursor1
    INTO @AWID, @Title, @MgrAWID, @Mgrtitle
    WHILE @@FETCH_STATUS = 0
    Begin
    Set @hstring = @AWID + ',' + @MgrAWID
    IF patindex('%CEO%', @Mgrtitle) > 0
    BEGIN
    --PRINT @hstring + ' is going in right away'
    INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@AWID, @hstring)
    END
    ELSE
    BEGIN
    --PRINT @hstring + ' begin subloop'
    mgrloop:
    Set @SEARCHAWID = @MgrAWID
    Declare cursor2 CURSOR FOR
    Select MgrAWID, MgrTitle
    FROM CCINFORMATION
    WHERE AWID = @SEARCHAWID

    OPEN cursor2

    FETCH NEXT FROM cursor2
    INTO @MgrAWID, @Mgrtitle

    WHILE @@FETCH_STATUS = 0
    Begin
    set @hstring = @hstring + ',' + @MgrAWID
    IF patindex('%CEO%', @Mgrtitle) > 0
    BEGIN
    --PRINT @hstring + ' is going into Hierarchy'
    INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@AWID, @hstring)
    CLOSE cursor2
    Deallocate cursor2
    set @hstring=''
    GOTO ceofound
    END
    ELSE
    BEGIN
    --PRINT @hstring + ' isnt ceo'
    CLOSE cursor2
    Deallocate cursor2
    GOTO mgrloop
    END
    END
    FETCH NEXT FROM cursor2
    INTO @MgrAWID, @Mgrtitle

    CLOSE cursor2
    Deallocate cursor2
    --PRINT @hstring + ' ends before ceo'
    set @hstring = @hstring + ',fail'
    INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@AWID, @hstring)
    set @hstring=''
    END

    ceofound:
    FETCH NEXT FROM cursor1
    INTO @AWID, @Title, @MgrAWID, @Mgrtitle
    END

    CLOSE cursor1
    DEALLOCATE cursor1
    -Doug Picanzi

Posting Permissions

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