CTE Error: "Types do not match between anchor and recursive part"

Question

I execute the following statement:

;WITH cte AS ( SELECT 1 as rn, 'name1' as nm UNION ALL SELECT rn + 1, nm = 'name' + CAST((rn + 1) as varchar(255)) FROM cte a WHERE rn < 10) SELECT * FROM cte 

... which ends with an error ...

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

Where am I making a mistake?

Answer 1

What he says:

'name1' has a different data type for 'name' + CAST((rn+1) as varchar(255))

Try this one (untested)

 ;with cte as ( select 1 as rn, CAST('name1' as varchar(259)) as nm union all select rn+1,nm = 'name' + CAST((rn+1) as varchar(255)) from cte a where rn<10) select * from cte 

In principle, you should also ensure that the length matches. For a recursive bit, you may need to use CAST('name' AS varchar(4)) if it doesn't work again

Answer 2

You need to display both fields nm

 ;with cte as ( select 1 as rn, CAST('name1' AS VARCHAR(255)) as nm union all select rn+1, nm = CAST('name' + CAST((rn+1) as varchar(255)) AS VARCHAR(255)) from cte a where rn<10) select * from cte 

Answer 3

For me, the problem was in a different comparison.

Only this helped me:

 ;WITH cte AS ( SELECT 1 AS rn, CAST('name1' AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS nm UNION ALL SELECT rn + 1, nm = CAST('name' + CAST((rn + 1) AS NVARCHAR(255)) AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT FROM cte a WHERE rn < 10) SELECT * FROM cte; 

Hope this can help someone else.

Answer 4

 ;with cte as ( select 1 as rn, 'name' + CAST(1 as varchar(255)) as nm union all select rn+1,nm = 'name' + CAST((rn+1) as varchar(255)) from cte a where rn<10) select * from cte 

Answer 5

In my case, I messed up the column sequence in the upper and lower UNION ALL clauses. And it turned out that the varchar column appeared "under" the int . Easy mistake to make you many columns

Answer 6

 ;with tmp1(NewsId,DataItem ,HeaderText) as ( select NewsId, LEFT(HeaderText, CHARINDEX(',',HeaderText+',')-1), STUFF(HeaderText, 1, CHARINDEX(',',HeaderText+','), '') from Currentnews union all select NewsId, LEFT(HeaderText, CHARINDEX(',',HeaderText+',')-1), STUFF(HeaderText, 1, CHARINDEX(',',HeaderText+','), '') from tmp1 where HeaderText > '' ) select NewsId, DataItem from tmp1 order by NewsId