Recursive sproc/function
-
I need to write a sproc/udf (doesn't matter which) that will recursively retrieve records from a combination of two tables. Table A contains records that identify a user and organization(s) they have permission to access. However, table B contains a parent-child relationship for organizations. If someone in table A has permission to an organization that has children in table B, they also inherit permission to the child organizations. Here's some sample data.
TABLE A
-------
USER ORG
TOM 1
TOM 7
SAM 5
AL 3
AL 8
MIKE 7
TABLE B
-------
ORG PARENT
1 NULL
2 1
3 1
4 2
5 2
6 3
7 NULL
8 7
The results for TOM would be: "1, 2, 3, 4, 5, 6, 7, 8" since TOM has permissions to the "root" level orgs 1 and 7 plus all their children and their grandchildren. NOTE - the recursion may be more than three levels in the real-world.
The results for SAM would be: "5" since 5 has no children.
The results for AL would be: "3, 6, 8"
The results for MIKE would be: "7, 8"
Note that I'd like the results in a table/recordset versus a comma-delimited string to I can work with them using an ADO recordset.
Any thoughts? TIA... Steve
-
This is an offshoot of the solution suggested in BOL under the "hierarchical information" heading....
SET NOCOUNT ON
CREATE TABLE #TabA(Name Varchar(10),ID int)
CREATE TABLE #TabB(ChildID int,ParentID int)
CREATE TABLE #Stack(ID int,Level int)
CREATE TABLE #Results(ParentName Varchar(10),ParentID int,ChildID int)
INSERT #TabA(Name,ID)
SELECT 'Tom',1
UNION ALL
SELECT 'Tom',7
UNION ALL
SELECT 'Sam',5
UNION ALL
SELECT 'Al',3
UNION ALL
SELECT 'Al',8
UNION ALL
SELECT 'Mike',7
Insert #TabB(ChildID,ParentID)
SELECT 1,NULL
UNION ALL
SELECT 2,1
UNION ALL
SELECT 3,1
UNION ALL
SELECT 4,2
UNION ALL
SELECT 5,2
UNION ALL
SELECT 6,3
UNION ALL
SELECT 7,NULL
UNION ALL
SELECT 8,7
--SELECT * FROM #TabA
--SELECT * FROM #TabB
Declare @ID int
Declare @CurrID int
Declare @Level int
Declare @Name Varchar(10)
SET @Name = 'Tom'
DECLARE Name_CUR CURSOR FOR
SELECT [ID] FROM #TabA WHERE [Name] = @Name
FOR READ ONLY
OPEN Name_CUR
FETCH NEXT FROM Name_CUR INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CurrID = @ID
INSERT #Stack([ID],[Level]) SELECT @CurrID,1
SET @Level = 1
WHILE @Level > 0
BEGIN
IF EXISTS(Select 1 FROM #Stack WHERE Level = @Level)
BEGIN
SELECT @CurrID = [ID] FROM #Stack WHERE [Level] = @Level
INSERT #Results(ParentName,ParentID,ChildID)
SELECT @Name,@ID,@CurrID
DELETE #Stack WHERE [Level] = @Level and [ID] = @CurrID
INSERT #Stack([ID],[Level])
SELECT ChildID,@Level+1 FROM #TabB WHERE ParentID = @CurrID
IF @@ROWCOUNT > 0
SET @Level = @Level + 1
END
ELSE
SET @Level = @Level - 1
END
FETCH NEXT FROM Name_CUR INTO @ID
END
CLOSE Name_CUR
DEALLOCATE Name_CUR
SELECT * FROM #Results ORDER BY ChildID
DROP TABLE #Stack
DROP TABLE #TabA
DROP TABLE #TabB
DROP TABLE #Results
SET NOCOUNT OFF
HTH
Temp tables...cursors...while loops....
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply