Create function GetChild (@id int) returns @tempTable table ( id int, subjectName varchar(100), subjectCode varchar(50) ) as begin insert @tempTable select id,name,code from subject where parentid = @id while @@rowcount > 0 insert @tempTable select a.id,a.name,a.code from subject as a inner join @tempTable as b on a.parentid = b.id and a.id not in(select id from @tempTable) return end Create Table Subject ( id int, name varchar(100), parentId int, code varchar(100) )