When we will create menus that time we will use recursive function:
ID ParentID
17.253 16.40
16.40 6.17
6.17 1.1
1.1 0
Very first time i will send 17.253 as input
second time In the first result set i will take (17.253,16.40)
i will take 16.40 as input
thrird time In the first result set i will take (16.40 ,6.17)
i will take 6.17 as input
‘
;
;
;
upto input 1.1
create PROCEDURE [recursivemenuProc]
–TestProc 25.12
–TestProc 17.253
@strID VARCHAR(20)
AS
DECLARE @parentId varchar(20)
DECLARE @tablename varchar(10)
DECLARE @strSql varchar(8000)
DECLARE @id varchar(20)
DECLARE @strGlobal varchar(20)
set @strGlobal = @strID
create table #tempTable(strID VARCHAR(20),strParentID varchar(20))
while (@strID <>’1.1′)
Begin
If Exists (Select Top 1 1 from #tempTable)
Begin
select @id = strID ,@parentId =strParentID from #tempTable
set @tablename =substring(@parentId,1,charindex(‘.’,@parentId)-1)
set @strSql =’select id, ParentID FROM @tablename WHERE ID=’+ @parentId
insert into #tempTable exec(@strSql)
End
Else
Begin
set @tablename =substring(@strID,1,charindex(‘.’,@strID)-1)
set @strSql =’select id, ParentID FROM @tablename WHERE ID=’+ @strID
insert into #tempTable exec(@strSql)
End
Select @strID =Isnull (@parentId, ’0′)
print @strID
End
