April 12, 2002 at 8:41 am
I have a recursive relationship in a table. Each record has a ParentID field that specifies its parent. I'm trying to write a recursive stored procedure that will give me a comma seperated list of IDs under a given ParentID. How can you write a recursive stored procedure that does that? The cursor is persistant across stored procedures, and that messes everything up... What I want is to be able to pass in a ParentID and get a concatenated list of all child ids - like pass in 1, get '2, 4, 5, 7, 19'. Then I can do an IN() on that id to find all the children. I don't need structure in the output, I just need to find out the ids of all children... Is there any way to do this?
April 12, 2002 at 8:53 am
DO you also need to get the childrens children in this process?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 12, 2002 at 8:55 am
Yes. That is the problem. I need to go n levels deep. N should be less than 6, but I still need to drill down like that.
April 12, 2002 at 9:47 am
Something like this should do the job. Basically this is based on a table (tblEmployees) where the person has an IDENTITY field INDEX and the ReportingMgr field is the reporting manager. This goes thru from a manager number and finds all the children and sub-children. Tested and worked fine.
CREATE PROCEDURE ip_GetChildList
@RepMgr VARCHAR(500)
AS
SET NOCOUNT ON
DECLARE @OutRepMgr AS VARCHAR(8000)
SET @OutRepMgr = @RepMgr
CREATE TABLE #tempTbl (
[mgrval] [int] NOT NULL
)
INSERT INTO #tempTbl (mgrval) EXEC ip_GetChildren @RepMgr
WHILE (SELECT COUNT(*) FROM #tempTbl) > 0
BEGIN
SET @RepMgr = ''
SELECT @RepMgr = @RepMgr + CAST(mgrval AS VARCHAR(10)) FROM #tempTbl
SET @OutRepMgr = @OutRepMgr + ', ' + @RepMgr
INSERT INTO #tempTbl (mgrval) EXEC ip_GetChildren @RepMgr
END
DROP TABLE #tempTbl
SELECT @OutRepMgr AS MgrVals
GO
CREATE PROCEDURE ip_GetChildren
@RepMgr VARCHAR(500)
AS
SET NOCOUNT ON
EXEC ('SELECT [INDEX] AS MgrVal FROM tblemployees WHERE ReportingMgr IN (' + @RepMgr + ')')
GO
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 12, 2002 at 10:32 am
Here's what I came up with:
CREATE procedure calc_EMPLTree (
@ParentEMPL_ID varchar(12),
@EMPL_IDList varchar(8000) output)
as
set nocount on
declare @EMPL_ID varchar(12)
declare @TempEMPL_IDList varchar(8000)
set @EMPL_IDList = @ParentEMPL_ID
declare userCursor cursor local FORWARD_ONLY
for select EMPL_ID from tblUser where ParentEMPL_ID=@ParentEMPL_ID
for read only
open userCursor
fetch next from userCursor into @EMPL_ID
while @@Fetch_Status = 0
begin
exec calc_EMPLTree @EMPL_ID, @EMPL_IDList=@TempEMPL_IDList output
if (NOT @TempEMPL_IDList IS NULL)
begin
if (@EMPL_IDList IS NULL)
set @EMPL_IDList = @TempEMPL_IDList
else
set @EMPL_IDList = @EMPL_IDList + ', ' + @TempEMPL_IDList
end
fetch next from userCursor INTO @EMPL_ID
end
close userCursor
deallocate userCursor
GO
I'm not sure how efficient that is, but it works.
April 12, 2002 at 10:40 am
As long as it works I say use it, then evaluate it later to make better. The only major thing I see is the use of a cursor which I was avoiding in mine to keep down the memory overhead. Other than that thou looks great to me.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 12, 2002 at 10:46 am
Yeah. This isn't really performance critical, because it'll be run only once a day at midnight. I just need something that works<g>.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply