Create Table A
(
    ParentGroupId   int ,
    SubGroupId  int

)

insert A select 1,2
insert A select 2,3
insert A select 3,4
go

create Proc Select_A(@ParentGroupId int)
as 
declare @lev int
declare @T table(ParentGroupId int,SubGroupId int,lev int)
set @lev=1
insert @T select ParentGroupId,SubGroupId,@lev from A where ParentGroupId=@ParentGroupId
while @@rowcount>0
begin
   set @lev=@lev+1
   insert @T select A.ParentGroupId,A.SubGroupId,@lev from A,(select * from @T where lev=@lev-1) T where A.ParentGroupId=T.SubGroupId
end
select ParentGroupId,SubGroupId from @T
go

exec Select_A 2

------解决方案--------------------
Create Table B
(
   ParentGroupId   nvarchar ,
   SubGroupId  nvarchar  

)

insert B select '1','2'
insert B select '2','3'
insert B select '3','4'
go

alter Proc Select_B(@ParentGroupId nvarchar )
as  
declare @lev int
declare @T table(ParentGroupId char,SubGroupId char,lev int)
set @lev=1
insert @T select ParentGroupId,SubGroupId,@lev from B where ParentGroupId=@ParentGroupId
while @@rowcount>0
begin
  set @lev=@lev+1
  insert @T select B.ParentGroupId,B.SubGroupId,@lev from B,(select * from @T where lev=@lev-1) T where B.ParentGroupId=T.SubGroupId
end
select ParentGroupId,SubGroupId from @T
go

exec Select_B '1'

------解决方案--------------------
Create Table A  
(  
   ParentGroupId   nvarchar ,  
   SubGroupId  nvarchar 

)  

insert A select  'a', 'b'  
insert A select  'b', 'c'  
insert A select  'c', 'd'  
go  

Create Proc Select_A(@ParentGroupId nvarchar )  
as 
declare @lev int  
declare @i int  
declare @T table(ParentGroupId nvarchar,SubGroupId nvarchar,lev int)  
set @lev=1  
set @i=1  
insert @T select ParentGroupId,SubGroupId,@lev from A where ParentGroupId=@ParentGroupId  
while @i <(select count(1) from A)
begin  
  set @lev=@lev+1  
  set @i=@i+1  
  insert @T select A.ParentGroupId,A.SubGroupId,@lev from A,(select * from @T where lev=@lev-1) T where A.ParentGroupId=T.SubGroupId  
end  
select ParentGroupId,SubGroupId from @T  
go  

exec Select_A  'b'