Types don't match between the anchor and the recursive part in column "Text" of recursive query "RecursiveCTE"

Question

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 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,
        _Emp.EmpName 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

Answer 1

The error message is pretty clear - have you checked what the datatypes are for Division from tblEmployees and for _Emp.EmpName from tblEmployees?

Since you're combining these in a UNION ALL in your RecursiveCTE, they need to be the same.

Either ensure they are in fact the same datatype in their respective tables, or apply a CAST() operator to one of them to make them the same, when you select them inside the RecursiveCTE.