递归表值函数向表变量插入行报错及实现需求咨询

首先，咱们先拆解你遇到的错误：Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnList", or the name is ambiguous.，这个问题核心出在递归调用的写法上——表值函数返回的是完整表结构，你不能直接在SELECT后写函数名，必须通过SELECT * FROM dbo.fnList(...)来获取它返回的行数据。

另外还有个关键漏洞：你的函数目前没有递归终止条件，会无限递归下去，最终肯定会触发报错。咱们一步步来修正：

1. 修正递归调用的语法 #

把你出错的那行INSERT语句改成下面的写法，就能正确读取表值函数返回的数据：

INSERT INTO @res 
SELECT * FROM dbo.fnList(Results.id, Results.parent) 
FROM @res Results 
WHERE Results.[table] = 'SomeValue' AND Results.parent = @id

2. 添加递归终止条件 #

你得明确告诉函数什么时候停止递归，比如当没有符合[table] = 'SomeValue'且parent = @id的记录时，就终止递归。同时我给初始的INSERT加了过滤条件，避免每次调用都插入全表数据导致重复：

CREATE function [dbo].[fnList](@id INT, @parent INT) 
RETURNS @res TABLE( [id] INT, [parent] INT, [parent_parent] INT, [amount] FLOAT, [cost] FLOAT, [table] VARCHAR(100) ) 
BEGIN 
    -- 插入当前层级的目标数据，避免全表重复插入
    INSERT INTO @res 
    SELECT * FROM SomeTable WHERE parent = @id;

    -- 检查是否存在需要递归的记录，有则继续调用
    IF EXISTS(SELECT 1 FROM @res WHERE [table] = 'SomeValue' AND parent = @id)
    BEGIN
        INSERT INTO @res 
        SELECT * FROM dbo.fnList(Results.id, Results.parent) 
        FROM @res Results 
        WHERE Results.[table] = 'SomeValue' AND Results.parent = @id
    END

    RETURN;
END

3. 更高效的替代方案：递归CTE #

在SQL Server中，递归表值函数的性能通常不如递归CTE，而且写法更简洁。如果你可以用内嵌表值函数，推荐用下面的实现：

CREATE FUNCTION [dbo].[fnListCTE](@id INT, @parent INT)
RETURNS TABLE
AS
RETURN (
    WITH RecursiveCTE AS (
        -- 锚点成员：初始层级的数据
        SELECT id, parent, parent_parent, amount, cost, [table]
        FROM SomeTable
        WHERE parent = @id
        UNION ALL
        -- 递归成员：基于上一层级的数据获取下一层
        SELECT st.id, st.parent, st.parent_parent, st.amount, st.cost, st.[table]
        FROM SomeTable st
        JOIN RecursiveCTE rc ON st.parent = rc.id
        WHERE rc.[table] = 'SomeValue'
    )
    SELECT * FROM RecursiveCTE
);

这个写法逻辑更清晰，性能也更优，还能避免多语句表值函数的一些潜在问题。

最后提个小建议：[table]是SQL Server的保留字，作为列名尽量用方括号包裹（你已经做了），或者改成非保留字的名称，减少潜在语法冲突。

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