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

Msg 240, Level 16, State 1, Line 4
Types don't match between the anchor and the recursive part in column "a" of recursive query "CTE".

     "Types don't match between the anchor and the recursive part" says that when you are using Common Table Expression (CTE), a perticular column "a"(as here mentioned in the error message) has a different DataType in Anchor Part with that of in Recursive Part. Anchor part is the part above the "UNION ALL" and Recursive Part is the part below it.

     Please note that the column DataType in these parts is not automatically converted to a higher precedence DataType to make a match. See the example below :

----- Create a table -------





CREATE TABLE TestTable

(

 UserID     VARCHAR(10)

,UserName   VARCHAR(50)

,ParentID   VARCHAR(10)

)



----- Insert the records into TestTable -------





INSERT INTO TestTable VALUES
(10001,'User1',NULL)

DECLARE @LOOPCOUNT
INT, @UserID INT, @ParentID INT

SET @LOOPCOUNT
= 1

SET @UserID = 10001

SET @ParentID = NULL

WHILE (@LOOPCOUNT < 50)

   BEGIN

       INSERT INTO TestTable

       SELECT  @UserID+@LOOPCOUNT
AS UserID 

              ,'User' + CAST(@LOOPCOUNT AS VARCHAR) AS UserName

              ,@UserID+@LOOPCOUNT-1 AS ParentID

       SET @LOOPCOUNT = @LOOPCOUNT + 1

   END

Now see the records inserted in the table TestTable :

SELECT * FROM TestTable



 UserID    UserName    ParentID

 10001    User1        NULL

 10002    User1        10001

 10003    User2        10002

 10004    User3        10003

 10005    User4        10004

 …….        …….        …….

   
 Now if you wish to see the users mapped and down the hierarchy of UserID '10001' and executed the below query :

;WITH CTE

AS

(

SELECT  '10001' UserID

       ,'User1' UserName

       ,NULL    ParentID

UNION  ALL 

SELECT TT.UserID, TT.UserName, TT.ParentID

FROM    TestTable  TT

       INNER
JOIN CTE

       ON
CTE.UserID = TT.ParentID

)

SELECT * FROM CTE

  
You will get the error message:

Msg 240, Level 16, State 1, Line 1

Types don't match between the anchor and the recursive part in column "UserID" of recursive query "CTE".

 This is because, in Anchor Part UserID has value '10001' whose length is of 5 characters so its datatype will be char(5), but in TestTable it is decalred as VARCHAR(10). The same is applied to UserName and ParentID as well.

To avoid this error, make a datatype casting. See below :

;WITH CTE

AS

(

SELECT  CAST('10001' AS VARCHAR(10)) UserID

       ,CAST('User1' AS VARCHAR(50)) UserName

       ,CAST(NULL AS VARCHAR(10)) ParentID

UNION  ALL 

SELECT TT.UserID, TT.UserName, TT.ParentID

FROM    TestTable  TT

       INNER
JOIN CTE

       ON
CTE.UserID = TT.ParentID

)

SELECT * FROM CTE

  
This time you will not get the error, and will have the output as below:

 UserID    UserName    ParentID

 10001    User1        NULL

 10002    User1        10001

 10003    User2        10002

 10004    User3        10003

 10005    User4        10004

 …….        …….        …….

 Note: If hierarchy has quite a high and recursion goes beyond 100, then you will face another error as:

Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Reference: Govind Badkur(http://govindbadkur.blogspot.com)