sqlserver 表值函数实现 递归 ID，PID 模式

sql 代码

USE [RIFA_IT_PLATFORM]
GO
/****** 对象:  UserDefinedFunction [dbo].[Get_Pid]    脚本日期: 08/15/2012 17:15:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Get_Pid](@ID varchar(50))
RETURNS @t_Level TABLE(ID varchar(50),Level int,unitname varchar(50),labellength varchar(50))
AS
BEGIN
 DECLARE @Level int,@unitname varchar(50),@labellength varchar(50)
 SET @Level=1
 INSERT @t_Level SELECT @ID,@Level,@unitname,@labellength
 WHILE @@ROWCOUNT>0
 BEGIN
  SET @Level=@Level+1
  INSERT @t_Level SELECT a.PUnitID,@Level,a.unitname,a.labellength
  FROM ORGStdStruct a,@t_Level b
  WHERE a.unitid=ID
   AND b.Level=@Level-1
 END
 RETURN
END

查询语句 select * from Get_Pid('要查询的子ID')