Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL SERVER 2000 Recursive Stored Procedure

Posted on 2004-04-03
8
1,414 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
0
Comment
Question by:flyin69
8 Comments
 
LVL 11

Accepted Solution

by:
vc01778 earned 250 total points
ID: 10748661
You can do it like this:

-- Given

create table t1(Name varchar(20), CategoryID int, ParentID int)

insert into t1 values('Politics',        1,    0)
insert into t1 values('Repub.  ',      2  ,  1)
insert into t1 values('Bush    ',       3 ,   2)
insert into t1 values('Democrat',    4    ,1)
insert into t1 values('Clinton ',       5 ,   4)
insert into t1 values('Cars    ',       6 ,   0)
insert into t1 values('Chevy   ',      7  ,  6)
insert into t1 values('Vette   ',       8 ,   7)

The procedure will look like this:

===============================================
alter procedure traverse(@current int) as
 set nocount on
 declare @Name varchar(20), @CategoryID int, @level int, @result varchar(4000)
 declare @stack table(Name varchar(20), CategoryID int, level int)
 declare @tmp table(CategoryID int, Name varchar(4000))

 if @current > 0
   insert into @tmp values(@current, '')
 else
   insert into @tmp select CategoryID, '' as x from t1 where ParentID=0

 while 1 = 1 begin
   select top 1 @current = CategoryID from @tmp where len(Name) = 0
   if @@rowcount = 0 break
 
   insert into @stack select Name, CategoryID, 1
   from t1 where CategoryID=@current
 
   -- find all the children for a given CategoryID
   set @level = 1
   set @result=''
   while @level > 0
              begin
                  if exists (select 1 from @stack where level = @level)
                      begin
                          select top 1 @Name=Name, @CategoryID=CategoryID
                          from @stack
                          where level=@level
                          set @result = @result + case when len(@result)>0 then '||' else '' end
                                   + @Name + ':' + cast(@CategoryID as varchar)
                          delete from @stack
                          where level = @level and CategoryID = @CategoryID
                          insert into @stack
                          select Name, CategoryID, @level + 1
                          from t1
                          where ParentID = @CategoryID
                          if @@rowcount > 0 set @level= @level + 1
                      end
                  else
                      set @level = @level - 1
   end
   
   update @tmp set Name = @result where CategoryID=@current
 end

-- show the result
 select Name from  @tmp
============================================

Then you can call the proc so:

traverse 2

Repub.  :2||Bush    :3

---
traverse 1

Politics:1||Repub.  :2||Bush    :3||Democrat:4||Clinton :5

---
traverse 0

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

VC
0
 

Author Comment

by:flyin69
ID: 10748724
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
0
 

Author Comment

by:flyin69
ID: 10749732
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
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10752456
>>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?
http://www.experts-exchange.com/help.jsp#hi68

Thanks.
0
 

Author Comment

by:flyin69
ID: 10753112
Thank you Ace
0
 

Expert Comment

by:scottelkin
ID: 11589034
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
0
 

Expert Comment

by:scottelkin
ID: 11589039
sorry, change all comments of "employee" to "category"
0
 

Author Comment

by:flyin69
ID: 11593863
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!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question