SQL表值函数内SELECT为变量赋值报错的解决方法

问题根因 #

你收到的报错 An INSERT statement cannot contain a SELECT statement that assigns values to a variable，是因为T-SQL语法不允许在INSERT ... SELECT语句的SELECT子句中同时为局部变量赋值。
你代码里的这段逻辑：

INSERT into @hiT
SELECT Top 1 @hi= HIERARCHY
FROM dbo.Company as C1
WHERE C1.Id = @SupervisorID;

同时执行了两个操作：往表变量@hiT插入查询结果、给局部变量@hi赋值，属于语法禁止的写法。
而且你定义的@hiT表变量在后续逻辑中完全没有被使用，属于冗余代码，可以直接删掉。

修正方案 #

去掉多余的@hiT表变量定义，单独编写SELECT语句给@hi变量赋值即可，修正后的完整函数代码如下：

CREATE FUNCTION GetSubTree (@SupervisorID int)
RETURNS @Result TABLE(
    Id  INT     PRIMARY KEY NOT NULL,
    HIERARCHY   HIERARCHYID    NOT NULL ,
    FirstName   NVARCHAR(50)    NOT NULL,
    LastName   NVARCHAR(50)    NOT NULL,
    NationalCode    bigint    NOT NULL,
    EmployeeRole    NVARCHAR(3) NOT NULL
)
AS
BEGIN
    DECLARE @hi HIERARCHYID;
    -- 单独查询获取指定主管的层级ID
    SELECT TOP 1 @hi = HIERARCHY
    FROM dbo.Company AS C1
    WHERE C1.Id = @SupervisorID;

    -- 可选校验：如果传入的主管ID不存在，直接返回空结果，避免无效全表扫描
    IF @hi IS NULL
        RETURN;

    -- 查询所有下属员工
    INSERT INTO @Result
    SELECT * 
    FROM dbo.Company AS C
    WHERE C.HIERARCHY.IsDescendantOf(@hi) = 1 AND C.Id != @SupervisorID;
    
    RETURN;
END;

补充说明 #

如果Company表的HIERARCHY字段建立了对应索引，IsDescendantOf方法的查询效率会很高，不需要额外编写递归CTE处理层级关系，当前写法性能符合常规业务要求。

内容的提问来源于stack exchange，提问作者Alireza Bayat Khani