UDF recursion and Cursors
-
Hello,
I have a table called "Domain" which looks like this :
DomainID int IDENTITY PRIMARY KEY
Domain varchar (50)
ParentDomainID int REFERENCES Domain.DomainID
I wrote a UDF called dbo.GetDomainChildren (ParentDomainID), which returns a table containing the children of a given DomainID.
CREATE FUNCTION dbo.GetDomainChildren (@ParentDomainID int)
RETURNS @myTable TABLE (DomainID int, Domain varchar(50), ParentDomainID int)
AS
BEGIN
DECLARE @myDomainID int
DECLARE @myDomain varchar(50)
DECLARE @myParentDomainID int
-- Create a cursor to store all immediate children of the given ParentDomainID
DECLARE myCursor CURSOR
LOCAL
FOR
SELECT DomainID, Domain, ParentDomainID
FROM Domain
WHERE ParentDomainID = @ParentDomainID
-- 1. Insert each child into the result table called myTable,
-- 2. Get each child's children recursively and insert them to myTable as well
OPEN myCursor
FETCH NEXT FROM myCursor INTO @myDomainID, @myDomain, @myParentDomainID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @myTable (DomainID, Domain, ParentDomainID) VALUES (@myDomainID,@myDomain, @myParentDomainID)
INSERT INTO @myTable (DomainID, Domain, ParentDomainID)
SELECT DomainID, Domain, ParentDomainID
FROM dbo.GetDomainChildren (@myDomainID)
FETCH NEXT FROM myCursor INTO @myDomainID, @myDomain, @myParentDomainID
END
RETURN
ENDAs you see, I used a cursor to iterate through each child domain to feed its domainID to the recursive call to GetDomainChildren UDF.
Does anyone have a better solution that doesn't make use of a cursor ?
- Danny
-
Have you tried doing a self-join,
eg:
select d1.DomainID , d2.DomainID
from Domain d1
inner join Domain d2
on d1.ParentDomainID = d2.DomainID
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply