sql - Types don't match between the anchor and the recursive part in column of recursive query -

given category @categoryid, query should recursively navigate top super-category, has been accomplished.

now i'd generate along string concatenation of categoryname in process.

declare @categoryid int = 217; categories (    select parentcategoryid, categoryname, '' strcategory    category     categoryid = @categoryid     union     select c.parentcategoryid, c.categoryname,        (c.categoryname + ': ' + cts.strcategory) strcategory      category c    join categories cts    on c.categoryid = cts.parentcategoryid )  select top 1 categoryname, len(categoryname) strlength categories order strlength desc 

with above code i'm getting following error:

types don't match between anchor , recursive part in column  "strcategory" of recursive query "categories". 

thanks helping

try changing query cast varchars varchar(max).

something like

declare @categoryid int = 217; categories (    select parentcategoryid, categoryname, cast('' varchar(max)) strcategory    category     categoryid = @categoryid     union     select c.parentcategoryid, c.categoryname,        cast((c.categoryname + ': ' + cts.strcategory) varchar(max)) strcategory      category c    join categories cts    on c.categoryid = cts.parentcategoryid )  select top 1 categoryname, len(categoryname) strlength categories order strlength desc