//由于是取根节点下第一层所对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