sql server - Using cursor to loop in SQL -

i have written sql code below return following result set. code allows user select datalibrary (parent) , find underlying datalibraries (child).

classification || dlgroup || datalibraryname || usedby || entityname || subentityname inuse          || mo      || singleorall     || mdl    || dashboarda || inuse          || mo      || singleorall     || mdl    || dashboardb || inuse          || mo      || singleorall     || mdl    || dashboardc ||   -- begin code -- set nocount on  declare @selecteddl varchar(50)  set @selecteddl = '1264'   --begin try  --create clustered index [clusteredindex-20130828-063538] on [dbo].[tblrpt_view]  --(  -- [view_type_id] asc  --)  --end try  --begin catch  -- there  --end catch   declare @pptreports table (  rpt_id integer  )   insert @pptreports  select rpt.rpt_id  dbo.tblrpt_main rpt  join dbo.tblrpt_view v  on v.rpt_id = rpt.rpt_id  , v.view_type_id = 1999    if isnull(@selecteddl,'') = '' set @selecteddl = '0'   select 'in use' classification,  dlg.group_name dlgroup,  dl.datasource_name datalibraryname,  'report' usedby,  r.rpt_name entityname,  convert(varchar(255),'') subentityname  dbo.tblrpt_datascript dl  join dbo.tblrpt_datascript_group dlg  on dlg.d_group_id = dl.datascript_group  join dbo.tblrpt_main r  on r.rpt_data_source = dl.datasource_id  (dl.datasource_id = @selecteddl  or @selecteddl = '0')  union  select 'in use' classification,  dlg.group_name,  dl.datasource_name datalibraryname,  'legacy chart' category,  r.rpt_name entityname,  c.control_name subentityname  dbo.tblrpt_datascript dl  join dbo.tblrpt_datascript_group dlg  on dlg.d_group_id = dl.datascript_group  join dbo.tblrpt_chart c  on c.datasource_id = dl.datasource_id  join dbo.tblrpt_main r  on r.rpt_id = c.rpt_id  (dl.datasource_id = @selecteddl  or @selecteddl = '0')  union  select 'in use' classification,  dlg.group_name,  dl.datasource_name datalibraryname,  'merge data library' category,  mdl.datasource_name entityname,  '' subentityname  dbo.tblrpt_datascript dl  join dbo.tblrpt_datascript_group dlg  on dlg.d_group_id = dl.datascript_group  join dbo.tblrpt_datascript mdl  on mdl.datalibraryxml '%<datalibaryid>'+ convert(varchar,dl.datasource_id)+'</datalibaryid>%'  (dl.datasource_id = @selecteddl  or @selecteddl = '0')  union  select 'in use' classification,  dlg.group_name,  dl.datasource_name datalibraryname,  'prevalidation' category,  pv.name entityname,  '' subentityname  dbo.tblrpt_datascript dl  join dbo.tblrpt_datascript_group dlg  on dlg.d_group_id = dl.datascript_group  join dbo.prevalidators pv  on pv.datasourceid = dl.datasource_id  (dl.datasource_id = @selecteddl  or @selecteddl = '0')  union  select 'in use' classification,  dlg.group_name,  dl.datasource_name datalibraryname,  'matrix' category,  wfq.data_event_name entityname,  '' subentityname  dbo.tblrpt_datascript dl  join dbo.tblrpt_datascript_group dlg  on dlg.d_group_id = dl.datascript_group  join dbo.workflow_data_events wfq  on wfq.datasource_id = dl.datasource_id  (dl.datasource_id = @selecteddl  or @selecteddl = '0')  union  select 'in use' classification,  dlg.group_name,  dl.datasource_name datalibraryname,  'commentary template' category,  com.template_name entityname,  '' subentityname  dbo.tblrpt_datascript dl  join dbo.tblrpt_datascript_group dlg  on dlg.d_group_id = dl.datascript_group  join dbo.commentary_template com  on com.datalibrary_id = dl.datasource_id  (dl.datasource_id = @selecteddl  or @selecteddl = '0')  union  select 'in use' classification,  dlg.group_name,  dl.datasource_name datalibraryname,  'financial control' category,  c.name entityname,  '' subentityname  dbo.tblrpt_datascript dl  join dbo.tblrpt_datascript_group dlg  on dlg.d_group_id = dl.datascript_group  join dbo.financial_controls_data_source ds  on ds.datasource_id = dl.datasource_id  join dbo.financial_controls c  on c.id = ds.financial_control_id  (dl.datasource_id = @selecteddl  or @selecteddl = '0')  union  select 'in use' classification,  dlg.group_name,  dl.datasource_name datalibraryname,  'ppt child dl' category,  rpt.rpt_name entityname,  '' subentityname  @pptreports tmp  join dbo.tblrpt_main rpt  on rpt.rpt_id = tmp.rpt_id  join dbo.tblrpt_view v  on v.rpt_id = rpt.rpt_id  join dbo.tblrpt_datascript dl  on dl.datasource_id != rpt.rpt_data_source  , substring(v.custom_mechanism_xml,1,2000) '%<dlname>'+ dl.datasource_name+'</dlname>%'  join dbo.tblrpt_datascript_group dlg  on dlg.d_group_id = dl.datascript_group   (dl.datasource_id = @selecteddl  or @selecteddl = '0')  union  select 'not used',  dlg.group_name,  dl.datasource_name,  '' category,  '' entityname,  '' subentityname  dbo.tblrpt_datascript dl  join dbo.tblrpt_datascript_group dlg  on dlg.d_group_id = dl.datascript_group  (dl.datasource_id = @selecteddl  or @selecteddl = '0')  , not exists (  (select 1 dbo.tblrpt_main r r.rpt_data_source = dl.datasource_id)  union  (select 1 dbo.financial_controls_data_source fc fc.datasource_id = dl.datasource_id)  union  (select 1 dbo.tblrpt_chart c c.datasource_id = dl.datasource_id)  union  (select 1 dbo.prevalidators pv pv.datasourceid = dl.datasource_id)  union  (select 1 dbo.commentary_template com com.datalibrary_id = dl.datasource_id)  union  (select 1 dbo.tblrpt_datascript mdl mdl.datalibraryxml '%<datalibaryid>'+ convert(varchar,dl.datasource_id)+'</datalibaryid>%')  union  (select 1 dbo.tblrpt_view vv join @pptreports pp on pp.rpt_id = vv.rpt_id , substring(vv.custom_mechanism_xml,1,2000) '%<dlname>'+ dl.datasource_name+'</dlname>%')  )  order 1,2,3,4,5,6   -- end code -- 

i take step further , have 3 levels: parent dl, child dl, sub dl of child dl. think cursor best way keep looping until cannot find data library, unsure how write code. below data look:

datalibraryname     ||      parent singleorall         ||      dashboarda singleorall         ||      dashboardb singleorall         ||      dashboardc dashboarda          ||      countrya dashboarda          ||      countryb dashboardb          ||      continentu dashboardc          ||      atlantic1 dashboardc          ||      pacific3 dashboardc          ||      china1 

please