Erreur de CTE : "Les types ne correspondent pas entre l'ancre et la partie récursive"

Question

J'exécute la déclaration suivante :

;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

...qui se termine par l'erreur...

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".

Où est-ce que je fais l'erreur ?

Answer 1

Si vous utilisez CONCAT dans le terme récursif d'un rcte, étant donné que le type de sortie de concat est varchar(MAX), vous ne devez effectuer le casting de la colonne que dans la requête initiale :

WITH rcte AS (
    SELECT 1 AS nr, CAST('1' AS varchar(MAX)) AS trail
    UNION ALL
    SELECT nr+1, CONCAT(trail, '/', nr+1)
    FROM rcte
    WHERE nr < 5
)
SELECT * FROM rcte;

Answer 2

Je recommande d'utiliser nvarchar(max)

WITH CTE AS (
SELECT x,x_name FROM (VALUES (1,CAST('' AS nvarchar(MAX)))) AS     test(x,x_name)
UNION ALL
SELECT x + 1 x, CONCAT(x_name,x+1)  FROM CTE WHERE x < 10 )
SELECT * FROM CTE

Answer 3

WITH rcte AS (
    SELECT 1 AS nr, CAST('1' AS varchar(MAX)) AS trail
    UNION ALL
    SELECT nr+1, cast(CONCAT(trail, '/', nr+1) as varchar(max))
    FROM rcte
    WHERE nr < 5
)
SELECT * FROM rcte;

Answer 4

;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