6

I am trying to make something like the following work:

WITH results(n, string ) AS (
            SELECT 1,'lol'
            UNION ALL
            SELECT n+1, string + ' lol'
            FROM results
            WHERE n<6
            )
            SELECT * FROM results

But SQL doesn't seem to recognize the string concatenation in the second column and returns the error:

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

My desired output would be something like

1, lol

2, lol lol

3, lol lol lol

and so on

Improve this question
1
  • You probably want a tally table/function here instead of an rCTE, it will be much faster
    – Charlieface
    Feb 28, 2021 at 17:02

1 Answer 1

Reset to default
7

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
Improve this answer
4
  • That works, thanks! How come you need to cast this one but it works without issue for n? i.e. you don't need to write out CAST(n as INT)
    – Oscar
    Feb 26, 2021 at 10:26
  • @Oscar, you can use CAST(n as INT) , but it is already of type int, so you don't need to specify. If you have bigint values , then it's another story... and should cast it to bigint
    – Sabin B
    Feb 26, 2021 at 11:09
  • Ah, I see now using the SQL_VARIANT_PROPERTY that unless casted as VARCHAR(MAX) 'lol' will be inferred the type VARCHAR(3) which is why it doesn't work otherwise. I find it strange that you're not allowed to simply declare the column type when initiating the CTE like: WITH results(n INT, string VARCHAR(MAX)).
    – Oscar
    Feb 26, 2021 at 15:00
  • Yes, SQL_VARIANT_PROPERTY it's of a great helper
    – Sabin B
    Feb 26, 2021 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.