SQL SERVER 2000 Recursive Stored Procedure
Posted on 2004-04-03
Here is the scenario.
Name CategoryID ParentID
Politics 1 0
Repub. 2 1
Bush 3 2
Democrat 4 1
Clinton 5 4
Cars 6 0
Chevy 7 6
Vette 8 7
It's your basic Parent-Child relationship.
Politics is the the Parent to Republican and Democrat.
Republican is the parent to Bush
Democrat is the parent to Clinton.
well, I need a stored procedure that, with a given CategoryID, it will return me back a string
representing all of the children that belong to that specified categoryID in one field, in the form of something like,
if i specify Republican with a CategoryID=2 then:
if i specify Politics with a CategoryID=1 i would get returned:
One more twist to this would be that if i didn't specify a Category To Search Under,
It would return me back A Row for each Root Category with representation for the Child cateogories for that root. Like for instance,
If Category passed In = 0 then,
return back as many rows as there are root categories, so for the above example would return:
I really don't mind of it's in a ad-hoc query to be put inside of a view, but a stored procedure would suit this type of query best. Thanks ahead of time for the advice.