ms-sql递归调用

 今天早上碰到个问题，需要在MS_SQL中实现递归，两周前在整理数据的时候刚好实现了一个递归调用的方法，借此机会整理一下发上来，万一以需要可以自己拿出来用，不用再搜索 :)

当然为了方便搜索到的同学能够方便使用，也补充了使用的例子

----递归函数--------------------------------------------------------------------------

create  function dbo.f_get_data_by_recursion
(
 @group_father_id int
)
 returns @groups_result table (group_id int,group_name varchar(50),group_father_id int,last_count int)
begin
 declare @count as int
 set @count=1
 
 insert @groups_result select *,@count from groups where group_id=@group_father_id
 while @@rowcount<>0
 begin
  set @count=@count+1
  insert @groups_result
   select g.group_id,g.group_name,g.group_father_id,@count from groups g,@groups_result gr
    where g.group_father_id=gr.group_id and gr.last_count=@count-1
 end
 return     
end

GO

----调用举例--------------------------------------------------------------------------

create table dbo.groups
(
 group_id int,
 group_name varchar(50),
 group_father_id int
)

declare @loop as int
set @loop =1
while @loop < 1000
begin
 insert into groups (group_id,group_name,group_father_id) values (@loop,'name',@loop -1)
 set @loop =@loop +1
end

set @loop =2001
while @loop < 2500
begin
 insert into groups (group_id,group_name,group_father_id) values (@loop,'name',@loop -1)
 set @loop =@loop +1
end

select * from groups
select * from f_get_data_by_recursion(2001)

drop table dbo.groups
drop function dbo.f_get_data_by_recursion