SQL递归

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  http://www.shenjk.com
-- Create date: http://www.shenjk.com
-- Description: http://www.shenjk.com
-- =============================================
ALTER FUNCTION [dbo].[fun]
(
 @pid int
)
RETURNS
@t TABLE
(
 id int,[Name] nvarchar(50),ParentId int default(0),
 Depath int default(0),Url varchar(150),WebUrl varchar(150),
 [no] int,IsOutLink int,IsItem int,IsContent int,IsOperation int
)
AS
BEGIN
 insert into @t select * from t_SysMenu where parentid=@pid 
 declare @c int
 select @c=count(*) from t_SysMenu where parentid=@pid
 if(@c>0)
 begin
  declare @m_lPriceId int
  declare p_cursor1 cursor for select ID from t_SysMenu where parentid=@pid
  open p_cursor1
   fetch next from p_cursor1 into @m_lPriceId
   declare @loopi1 int
   set @loopi1=1 --设置计数器
   while @@FETCH_STATUS = 0
   begin
    insert into @t select * from fun(@m_lPriceId)
    --insert into @indextable(PID) values(@m_lPriceId)
    set @loopi1= @loopi1+1
    fetch next from p_cursor1 into @m_lPriceId
   end
  close p_cursor1
  DEALLOCATE p_cursor1
 end 
 RETURN
END