用proc进行树递归

 /**//****** Object:  Table [dbo].[Tree]    Script Date: 2005-11-04 18:07:00 ******/
 CREATE TABLE [dbo].[Tree] (
  [ID] [int] IDENTITY (1, 1) NOT NULL ,
  [PID] [int] NULL ,
  [Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
 ) ON [PRIMARY]
 GO
 
  CREATE  CLUSTERED  INDEX [IX_Tree] ON [dbo].[Tree]([PID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tree] WITH NOCHECK ADD
 CONSTRAINT [PK_Tree] PRIMARY KEY  NONCLUSTERED
 (
  [ID]
 )  ON [PRIMARY] ,
 CONSTRAINT [子ID不能等于父ID] CHECK ([ID] <> [PID])
GO

ALTER TABLE [dbo].[Tree] ADD
 CONSTRAINT [FK_Tree_Tree] FOREIGN KEY
 (
  [PID]
 ) REFERENCES [dbo].[Tree] (
  [ID]
 )
GO

/**//****** 对象:  用户定义的函数 dbo.fGetTreeTable    脚本日期: 2005-11-04 18:07:02 ******/
CREATE FUNCTION dbo.fGetTreeTable
 (
 @ID int= null
 )
RETURNS @Tab TABLE(ID int, PID int, Name varchar(10), Lev int)
AS
 BEGIN
  Declare @lev int
  Set @lev=0
 
  While @lev=0 or @@ROWCount>0
  Begin
   Set @Lev=@Lev+1
   Insert @Tab(ID, PID, Name, Lev)
   Select ID, PID, Name, @Lev From Tree Where (@Lev=1 and ((PID=@ID) or (@ID is null and PID is null))) or (PID in (Select ID From @Tab Where Lev=@Lev-1))
   order by ID
  End
  RETURN
 END

GO

--实际数据
Insert Tree(PID, [Name]) values(null, '公司')
Insert Tree(PID, [Name]) values(3, 'IT')
Insert Tree(PID, [Name]) values(1, 'Fin')
Insert Tree(PID, [Name]) values(4, 'XZ')
Insert Tree(PID, [Name]) values(1, 'HR')
Insert Tree(PID, [Name]) values(6, 'HR')
GO
select * from tree
delete from tree
Select * from dbo.fGetTreeTable(null)