MSSQL获取父节点方法

MSSQL获取父节点方法

CREATE FUNCTION [dbo].[Fn_GetSelfParentNodes] 
(    
    @NodeID bigint,--当前节点编号
    @IsContainSelf bit--是否包含当前节点
)
RETURNS @Table TABLE 
(
    NodeID bigint,
    NodeLevel int
)
AS
BEGIN
IF @NodeID <=0
BEGIN
    RETURN --无法节点
END
Declare @CID bigint
Declare @level int

    if(@IsContainSelf=0)--不包含当前节点
    BEGIN
        SELECT     @CID = ParentID from dbo.Nodes where NodeID= @NodeID --获取父节点
    END
    ELSE --包含当前节点
    BEGIN
        SET @CID = @NodeID    
    END
    SET @level=1
    INSERT INTO @Table SELECT @CID,@level
    
    While @@ROWCOUNT >0
    BEGIN
        SET @level=@level+1
        INSERT INTO @TABLE SELECT N.ParentID,@level FROM dbo.Nodes N,@Table t where t.NodeID=N.NodeID and t.NodeLevel=@level-1--变换父子叶
    END
    RETURN
END