CTE error: "Types don't match between the anchor and the recursive part"

Question

I am executing

;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

and is receiving the 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 I am making the mistake?

Thanks in advance

Answer 1

Exactly what it says:

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

Try this (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

Basically, you have to ensure the length matches too. For the recursive bit, you may have to use CAST('name' AS varchar(4)) if it fails again

Answer 2

You need to cast both nm fields

;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

;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