;with list
as
(
select c.* from
clsc.dbo.tcl_process_category c
inner join clsc.dbo.tcl_process_category_study s
on c.company_seq = s.company_seq
and c.category_no = s.category_no
where s.company_seq = @company_seq
and s.cp_code = @cp_cd
and s.study_cd = @process_cd
union all
select c.* from
clsc.dbo.tcl_process_category c
inner join list l
on c.company_seq = l.company_seq
and c.category_no = l.parent_category_no
where c.company_seq = @company_seq
)
select *
from list
where [level] = 1