Datatype mismatch in anchor and recursive part of a recursive CTE-CodePudding

I am running this SQL code on SQL Server to print alphabets from A to Z:

;with alphaCte as 
(
    select 'A' as letter
    union all
    select char(ascii(letter) 1) 
    from alphaCte 
    where letter < 'Z'
)
select * from alphaCte

I get this error:

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

To rectify it, I have make below change.

;with alphaCte as 
(
    select char(ascii('A')) as letter
    union all
    select char(ascii(letter) 1) 
    from alphaCte 
    where letter < 'Z'
)
select * from alphaCte

which works fine.

Could anyone please explain why my original code is throwing this datatype mismatch error?

CodePudding user response：

To expand on my comment

Select column_ordinal
      ,name
      ,system_type_name 
 From sys.dm_exec_describe_first_result_set('select ''A'' as letter,char(ascii(''A'') 1) as letter2',null,null )

Results

column_ordinal  name      system_type_name
1               letter    varchar(1)
2               letter2   char(1)

EDIT: Just an aside... Recursive CTEs are great but datasets are better :)

Select Top 26 C=char(64 Row_Number() Over (Order By (Select NULL)) ) 
 From  master..spt_values n

CodePudding user response：

I hope this explanation helps.

So ASCII stands for American Standard Code for Information Interchange. ASCII is used as a character encoding standard for modern computers. The ASCII() function accepts a character expression and returns the ASCII code value of the leftmost character of the character expression.

The following shows the syntax of the ASCII() function:

ASCII ( input_string ) The input_string can be a literal character, a character string expression, or a column. If the input_string has more than one character, the function returns the ASCII code value of its leftmost character.

The following example returns the ASCII code values of character A and Z:

SELECT 
    ASCII('AB') A, 
    ASCII('Z') Z;
Here is the output:

A           Z
----------- -----------
65          90

(1 row affected)