Function to retrieve hierarchical data

Hi,
I have a table looking aprox. like this:
OrganizationId, ParentOrganizationId, ManagerId
My problem is that I would like to show all sub units which a manager is
responsible for and not only the ones he is directly manager for. I have
solved it like below.
I can query it like this:
select * from GetReports('KMD', NULL, NULL)
I is actually working. I just have three questions:
1) Why are default parameters not working for functions? I get an error when
I try to call GetReports with two parameters
2) I am using (SELECT MIN(OrganizationId) FROM Organization WHERE
ParentOrganizationId = @.ParentOrganizationId instead of using cursors
because I have heard so many complaining about cursors. What is the right
way?
3) Is this actually 'Good' SQL code, or are there more efficient ways? It
has to run on SQL Server 2000
Thanks
Henrik Skak Pedersen
ALTER FUNCTION dbo.GetReports
(
@.ManagerId varchar(50),
@.ParentOrganizationId varchar(50),
@.Exclude int = NULL
)
RETURNS @.retGetHierarchy TABLE (OrganizationId varchar(50))
AS
BEGIN
DECLARE @.OrganizationId varchar(50)
DECLARE @.TmpManagerId varchar(50)
IF (@.ParentOrganizationId IS NULL)
SET @.OrganizationId = (SELECT MIN(OrganizationId) FROM Organization WHERE
ManagerId = @.ManagerId)
ELSE
SET @.OrganizationId = (SELECT MIN(OrganizationId) FROM Organization WHERE
ParentOrganizationId = @.ParentOrganizationId)
WHILE @.OrganizationId IS NOT NULL
BEGIN
SET @.TmpManagerId = (SELECT ManagerId FROM Organization Where
OrganizationId = @.OrganizationId)
IF (@.Exclude IS NULL OR @.TmpManagerId IS NULL OR @.ManagerId <>
@.TmpManagerId)
BEGIN
-- Insert current value
INSERT INTO @.retGetHierarchy VALUES( @.OrganizationId )
-- Insert child values
INSERT INTO @.retGetHierarchy
SELECT * FROM GetReports(@.ManagerId, @.OrganizationId, 1)
END
IF (@.ParentOrganizationId IS NULL)
SET @.OrganizationId = (SELECT MIN(OrganizationId) FROM Organization WHERE
ManagerId = @.ManagerId AND OrganizationId > @.OrganizationId)
ELSE
SET @.OrganizationId = (SELECT MIN(OrganizationId) FROM Organization WHERE
ParentOrganizationId = @.ParentOrganizationId AND OrganizationId >
@.OrganizationId)
END
RETURN
ENDMonkeys climb trees:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML|||>> 3) Is this actually 'Good' SQL code, or are there more efficient ways? It
has to run on SQL Server 2000 <<
Look up "nested Sets model" or get a copy of TREES & HIERARCHIES IN
SQL. You can do all of this in one simpel query.|||Hi,
Thank you very much for your reply.
I have now tried your example and that works well two. But is it really
faster than the solution which I have allready come up with? In your case I
guess I have to run through all entries of the table, when in mine I
recursively traverse the three?
Henrik,
"ML" <ML@.discussions.microsoft.com> wrote in message
news:74A5CF03-3150-438F-B30B-82026EB1106C@.microsoft.com...
> Monkeys climb trees:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
>
> ML|||Hi,
Thank you very much for your reply.
I have now tried to search as you have told me, and I have found some nice
articles, many of then by you :-)
But as I see it, I have to change the datemodel to use the concept which you
are descriping. That is unfortunately not possible. Is it the possible to
use your concept in another way?
Henrik.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122654159.437309.284240@.g47g2000cwa.googlegroups.com...
> Look up "nested Sets model" or get a copy of TREES & HIERARCHIES IN
> SQL. You can do all of this in one simpel query.
>|||No. Nothing can save you from bad DDL. In fact, I have a current
article at DM REVIEW that says that most bad SQL DML is the result of
bad DDL that forces loops, self-joins, nested queries, etc. on the
programmer.
However, it is easy to convert an adjacency list mode to a nested sets
model. You use that push-down stack algorithm from your freshman
college data structures class. It will run about 10 to 100 times
faster than a cursor.
Get a copy of TREES & HIERARCHIES IN SQL for most of the code you will
need.|||Thank you for your reply. I will get a copy of the book.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122763828.891859.8310@.z14g2000cwz.googlegroups.com...
> No. Nothing can save you from bad DDL. In fact, I have a current
> article at DM REVIEW that says that most bad SQL DML is the result of
> bad DDL that forces loops, self-joins, nested queries, etc. on the
> programmer.
> However, it is easy to convert an adjacency list mode to a nested sets
> model. You use that push-down stack algorithm from your freshman
> college data structures class. It will run about 10 to 100 times
> faster than a cursor.
> Get a copy of TREES & HIERARCHIES IN SQL for most of the code you will
> need.
>