We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL SERVER 2000 Recursive Stored Procedure

flyin69
flyin69 asked
on
Medium Priority
1,467 Views
Last Modified: 2008-03-04
Hello All,

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:
Republican:2||Bush:3

if i specify Politics with a CategoryID=1 i would get returned:
Politics:1||Republican:2||Bush:3||Democrat:4||Clinton:5

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:

Politics:1||Republican:2||Bush:3||Democrat:4||Clinton:5
Cars:6||Chevy:7||Vette:8


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.

Flyin
Comment
Watch Question

Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Wow, that looks great VC, I'm gonna give that a try and give you some feedback.  Thanks for the expert piece of mind!

Flyin

Author

Commented:
Amazing, thanks VC, that's exaclty what i was looking for!

I 've been looking for about 2 months now and finally on this site have seen the answer i was looking for.  thanks again.

Flyin
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>Amazing, thanks VC, that's exaclty what i was looking for!<<
This is where you thank the Expert(s) helping you by awarding them the points.  See here for more info:
How do I accept a comment?
https://www.experts-exchange.com/help.jsp#hi68

Thanks.

Author

Commented:
Thank you Ace
This is cleaner and probably faster:

create                    PROCEDURE ps_AllChildrenOfEmployee
 @categoryid int

AS

set nocount on

declare @rowcount int
 ,@qry varchar(1000)
 ,@template varchar(200)

--template that we will use to continue to nest sql statements
set @template = 'select categoryid from tblcategories where parentid in ( {0} )'

--used to store the query we will run each iteration
set @qry = 'select categoryid from tblcategories where parentid = ' + cast(@categoryid as varchar(5))

--we will get a list of all subordinates under this employee, no matter how deep
create table #temp(categoryid int)

--add themselves to the list of employees if you want
insert into #temp values(@categoryid)

--set rowcount to 1 so we can enter the loop
set @rowcount = 1

while @rowcount > 0
  begin
 --exec the last qry string. the first time through will run the default above
 exec ( 'insert into #temp ' + @qry )
 
 --get the # of records inserted.  if zero, then we want to exit the loop
 set @rowcount = @@rowcount

 --take this qry and nest it one more level deep so we get the next level down of employees
 set @qry = replace(@template,'{0}',@qry)

  end


drop table #temp
sorry, change all comments of "employee" to "category"

Author

Commented:
wow thank you  scottelkin, i will try that out.  Yes it does look much cleaner!  I'll get back to you on how successful i was!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.