Recursive Queries in SQL Server!! 1

[faisal]

Dear freiends,<br>
I'am new to SQL Server and want to know that does SQL server supports recursive queries like ORACLE does("connect by", "prior" clauses within PL/SQL).<br>
Let's suppose i've a database which can store categories for an auction site like eBay. The table structure would be like:<br>
Category_tbl<br>
------------<br>
CategoryID<br>
CategoryName<br>
ParentID<br>
CategoryDescription<br>
..<br>
..<br>
<br>
The data will be stored in hierarchical order.<br>
Please suggest.<br>
<br>
Thank You!<br>
-Faisal

 

[faisal]

Well I've found the soloution for my prblem and I'am explaining it for other users:<br>
For reterirving the hierarchical data as mentioned above we have to create a stored procedure and then we can call it from anywhere by just giving the starting point.<br>
The procedure below require a parameter i.e the categoryid from where to start finding the child nodes.<br>
<br>
**********************************************<br>
CREATE PROCEDURE FindHrchy (@current int) AS<br>
<br>
declare @ILevel int,@ILine char(32),@IPrint char(50)<br>
create table #temp_categ(cid int)<br>
create table #stack (item int,tlevel int)<br>
insert into #stack values (@current,1)<br>
select @iLevel=1<br>
While @iLevel&gt;=1<br>
<br>
begin<br>
if exists(select * from #stack where tLevel=@iLevel)<br>
begin<br>
Select @current=item from #Stack where tLevel=@iLevel<br>
<br>
Insert #temp_categ values (@Current)<br>
<br>
Select @iPrint=space((@iLevel-1)*2)+convert(char,@current)<br>
<br>
delete from #stack where tLevel=@iLevel and item=@current<br>
<br>
Insert #stack Select CID,@iLevel+1 from cmaster where PID=@Current<br>
<br>
if @@rowcount&gt;0<br>
Select @iLevel=@iLevel+1<br>
end<br>
else<br>
Select @iLevel=@iLevel-1 <br>
<br>
end<br>
Select a.cid,b.pid,b.levelNo,b.cname from #temp_categ a,cmaster b<br>
where a.cid=b.cid order by a.cid asc<br>
<br>
return @@ROWCOUNT<br>
GO<br>
<br>
IF anybody wants some kind of explanation, please email to me.<br>
Thanks<br>
-Faisal

 

[amahfouz]

dear Faisal
i would like to thank you first,and i need if u could send more declaration about stored procedure of the hirarchy query.
I used to beat this problem by Oracle &quot;start with --- connect by&quot;
and i need more details about this procedure u answered before