Hierarchy in SQL Table using stored procedure and cursor within cursor

Hyrarchy in SQL Table using stored procedure and cursor within cursor

--syntex for creating Table named hyrarchy

create table hyrarchy(id int primary key identity(1,1),catname

varchar(50),rel varchar(1),lev int,relid int)

--syntex for Inserting Data in  Table named hyrarchy

insert into hyrarchy(catname ,rel,lev ,relid) values('Student','p',0,0)

insert into hyrarchy(catname ,rel,lev ,relid) values('Book','c',1,1)

insert into hyrarchy(catname ,rel,lev ,relid) values('Pencil','c',2,1)

insert into hyrarchy(catname ,rel,lev ,relid) values('School','p',0,0)

insert into hyrarchy(catname ,rel,lev ,relid) values('Teacher','c',1,4)

insert into hyrarchy(catname ,rel,lev ,relid) values('Class','c',2,4)

insert into hyrarchy(catname ,rel,lev ,relid) values('Copy','c',3,1)

--syntex for creating stored procedure

create proc sp_hyrarchyoftable

as

begin

--syntex for declaring variables

declare @id int

declare @catname varchar(50)

declare @rel varchar(1)

declare @lev int

declare @relid int

declare @catnemrel varchar(100)

--syntex for declare cursor

declare cursorforlevel cursor fast_forward for select id,catname,rel,lev,relid from hyrarchy where relid=0

--syntex for open cursor

open cursorforlevel

--syntex for create temporary table

create table #temp1(id int,CategorName varchar(50),Relation varchar(1),Lvel int,Relation_id int,)

--fetching data row by row

fetch next from cursorforlevel into @id,@catname,@rel,@lev,@relid

--checking all rows are fetched

while @@FETCH_STATUS=0

begin

--fetched row data are inserted into temp table #temp1

insert into #temp1 values(@id,@catname,@rel,@lev,@relid)

--syntex for declare cursor

declare cursorforlevel1 cursor fast_forward for select id,catname,rel,lev,relid from hyrarchy where relid=@id

--syntex for open cursor

open cursorforlevel1

--fetching data row by row

fetch next from cursorforlevel1 into @id,@catname,@rel,@lev,@relid

--checking all rows are fetched

while @@FETCH_STATUS=0

begin

--fetched row data are inserted into temp table #temp1

insert into #temp1 values(@id,@catname,@rel,@lev,@relid)

--move data fetching in next row if all row data not fetched

fetch next from cursorforlevel1 into @id,@catname,@rel,@lev,@relid

end

-- Closing cursor 

close cursorforlevel1

-- Dealocate cursor from Memory 

deallocate cursorforlevel1

--move data fetching in next row if all row data not fetched

fetch next from cursorforlevel into @id,@catname,@rel,@lev,@relid

end

-- Closing cursor 

close cursorforlevel

-- Dealocate cursor from Memory 

deallocate cursorforlevel

select * from #temp1

end