递归查询RecursiveCTE的Text列锚点与递归部分类型不匹配如何解决？

错误原因 #

递归CTE的Text列在锚点查询和递归查询部分的数据类型/长度不匹配：

• 锚点部分的Text直接取自Division字段，未做显式类型转换
• 递归部分的Text取自EmpName字段，同样未做统一转换
  SQL执行UNION ALL时会严格校验对应列的数据类型一致性，因此触发类型不匹配错误。

解决方法 #

显式将锚点和递归部分的Text列转换为相同的数据类型及长度，比如统一使用VARCHAR(100)（可根据实际业务中Division和EmpName的最大长度调整）。

修改后的完整代码 #

DECLARE @RowCount INT;

WITH RecursiveCTE AS 
(
    SELECT 
        CAST(ROW_NUMBER() OVER (ORDER BY Division) AS VARCHAR(50)) AS ID,
        CAST(NULL AS VARCHAR(50)) AS ParentID,
        -- 显式转换Division为统一类型
        CAST(Division AS VARCHAR(100)) AS Text,
        CAST(NULL AS VARCHAR(MAX)) AS UserID
    FROM 
        (SELECT DISTINCT Division 
         FROM tblEmployees 
         WHERE UserType = 'Approver' 
           AND Division IN (SELECT AssignToDivision 
                            FROM tblDefineAssignTo 
                            WHERE Division = 'Pharma')
        ) x

    UNION ALL

    SELECT
        CAST(MAX(CAST(RecursiveCTE.ID AS INT)) OVER () + ROW_NUMBER() OVER (ORDER BY _Emp.EmpName) AS VARCHAR(50)) AS ID,
        CAST(RecursiveCTE.ID AS VARCHAR(50)) AS ParentID,
        -- 显式转换EmpName为相同类型
        CAST(_Emp.EmpName AS VARCHAR(100)) AS Text,
        CAST(_Emp.UserID AS VARCHAR(MAX)) AS UserID
    FROM 
        tblEmployees AS _Emp
    JOIN 
        RecursiveCTE ON _Emp.Division = RecursiveCTE.Text
),
OrderedCTE AS 
(
    SELECT
        ID,
        ParentID,
        Text,
        UserID,
        ROW_NUMBER() OVER (ORDER BY ParentID) AS OrderByParentID
    FROM 
        RecursiveCTE
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY OrderByParentID) AS ID,
    ParentID,
    Text,
    UserID
FROM 
    OrderedCTE
OPTION (MAXRECURSION 0); -- Use this option to handle deep recursion if needed

额外说明 #

• 选择VARCHAR(100)是通用适配方案，实际可查询tblEmployees表中Division和EmpName的字段定义（比如执行sp_help 'tblEmployees'），取两者中最大的长度值作为统一长度，避免截断数据。
• 确保所有对应列在锚点和递归部分的数据类型完全一致是递归CTE的核心要求之一，UNION ALL会自动做类型推导，但显式转换能避免隐式转换带来的问题。

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