sql查父节点小笔记

//由于是取根节点下第一层所对0作了一个判断 这里自已可以根据自已要的数据来取
ALTER function [dbo].[f_pid](@id varchar(60)) returns @t_level table(pid varchar(60),sortNum int)
as
BEGIN
  DECLARE @sortNum int 
  SET @sortNum = 0
  
  --得到当前id的父id, 
  select @id = ParentId, @sortNum =Sortnum 
  from Sys_Departments where keyId = @id and ParentId is not NULL
  
  while @@ROWCOUNT > 0
  BEGIN  --循环输入插入
     insert into @t_level select @id, @sortNum
     --------------------------------------------
     --继续找父id
     select @id = ParentId, @sortNum = Sortnum from Sys_Departments
     where keyId = @id and ParentId is not NULL 
     --------------------------------------- 
     
     IF @id<>'0'
        BEGIN
            DELETE @t_level --删除
        END
     ELSE
        BEGIN
            --只取当前根节点的下一个最高父节点
            BREAK
        END
         
  end
  return
end