You can cast it to varchar(2000) or varchar(max) depending on your needs
so both should have the same data type and size
for value 1, it using an integer/int data type
From BOL related to CTE ,
- The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
WITH results(n, string ) AS (
SELECT 1,CAST('lol' as varchar(2000))
UNION ALL
SELECT n+1, CAST(string + ' lol' as varchar(2000))
FROM results
WHERE n<6
)
SELECT * FROM results
you can see the data type by using SQL_VARIANT_PROPERTY
WITH results(n, string, expressionType1, expressionType2) AS (
SELECT 1,cast('lol' as varchar(2000)),
SQL_VARIANT_PROPERTY(1, 'BaseType'),
SQL_VARIANT_PROPERTY(cast('lol' as varchar(2000)), 'MaxLength')
UNION ALL
SELECT n+1, cast(string + ' lol' as varchar(2000)) ,
SQL_VARIANT_PROPERTY(n+1, 'BaseType'),
SQL_VARIANT_PROPERTY(cast(string + ' lol' as varchar(2000)), 'MaxLength')
FROM results
WHERE n<6
)
SELECT * FROM results
output:
n string expressionType1 expressionType2
1 lol int 2000
2 lol lol int 2000
3 lol lol lol int 2000
4 lol lol lol lol int 2000
5 lol lol lol lol lol int 2000
6 lol lol lol lol lol int 2000
Data type for value/expression 1
can be checked by:
select SQL_VARIANT_PROPERTY(1,'basetype')
int