Common Table Expressions

[gurubhai]

Hi

I was working with CTE's in SQL server 2005 and it is a new thing so me. I was trying to excute a simple query as follows:

with somename(x)
as
(
select x = convert(varchar(8000),'hello')
union all
select x + 'a' from somename where len(x) < 100
)
select x from somename
order by x

If I replace varchar(8000) with anything say varchar(100)or varchar(800) I keep getting the below error:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "x" of recursive query "somename".

This is kinda unexpected behaviour.
Do u have any idea???

[rhettss]

solution below.

with somename(x)
as
(
select x = convert(varchar(100),'hello')
union all
select cast((x + 'o') as varchar(100)) from somename where len(x) < 100
)
select x from somename
order by x

it seems that X's type is implicitly being set by the concatenation with 'a'. Without implicitly setting the length, X's length appears to default to 8000 in the recursion step. In my solution, X's implicit length is 8000, but I cast it down to 100.

What is especially interesting is if you append option (maxrecurion 90) to the bottom select: no results are found.

BUT!, if you remove the order by, the select statement with the OPTION MAXRECURSION succeeds!

[gurubhai]

Thanks buddy!!!
Its seems it wil take a lot of time to learn CTEs with all these confusions in place!!!