如何在SQL Server函数中查询指定节点的所有层级父节点

问题原因 #

你写的插入逻辑没有实现递归效果：普通SELECT ... UNION ALL ...语句只会单次执行，执行INSERT时@TempModelParents还未完成数据写入，第二部分关联查询只能读到空的表变量，自然只能查到初始的1条数据，无法逐层向上递归查找父节点。

正确实现方案 #

推荐直接使用内联表值函数封装递归CTE逻辑，性能比多语句表值函数更优，代码如下：

CREATE FUNCTION dbo.GetAllModelParents
(
    @InputModelId INT
)
RETURNS TABLE 
AS
RETURN
(
    WITH hierarchical AS (
        SELECT ModelId, ParentId
        FROM [dbo].[Models] 
        WHERE ModelId = @InputModelId
        UNION ALL
        SELECT parent.ModelId, parent.ParentId
        FROM [dbo].[Models] parent 
        INNER JOIN hierarchical child ON child.ParentId = parent.ModelId
    )
    SELECT * FROM hierarchical
);

调用示例 #

-- 查询ModelId=142对应的所有层级父节点
SELECT * FROM dbo.GetAllModelParents(142);

如果你确实需要用多语句表值函数（比如有额外逻辑需要处理中间数据），正确写法如下：

CREATE FUNCTION dbo.GetAllModelParents_MultiStatement
(
    @InputModelId INT
)
RETURNS @TempModelParents TABLE (ModelId INT, ParentId INT)
AS
BEGIN
    WITH hierarchical AS (
        SELECT ModelId, ParentId
        FROM [dbo].[Models] 
        WHERE ModelId = @InputModelId
        UNION ALL
        SELECT parent.ModelId, parent.ParentId
        FROM [dbo].[Models] parent 
        INNER JOIN hierarchical child ON child.ParentId = parent.ModelId
    )
    INSERT INTO @TempModelParents
    SELECT * FROM hierarchical;

    RETURN;
END;

内容的提问来源于stack exchange，提问作者Arash Bazrafshan