Query Help. About recursive CTE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help. About recursive CTE

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2010-05-12 : 11:55:28

Hi there. I have a problem with the maximum recursion limit. Here is the query:
-------------------------------------------------------
Declare @RepeatShuffling int
Set @RepeatShuffling = 10000
;With rc1 as

( Select Base1, 1 as a From #B

Union all

Select Base1, a+1 From rc1 Where a < @RepeatShuffling

)
--------------------------------------------------------
The error pump out as "The statement terminated. The maximum recursion 100 has been exhausted before statement completion".

Is there any way to complete this recursion?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 11:57:36

just give

OPTION (MAXRECURSION 0)

after selection statement from CTE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mavershang
Posting Yak Master

111 Posts

Posted - 2010-05-12 : 12:02:31

So you mean like this ?
--------------------------
;With rc1 as

( Select Base1, 1 as a From #B

Union all

Select Base1, a+1 From rc1 Where a < @RepeatShuffling

Option (Maxrecursion 0)
)
--------------------------------

quote:
Originally posted by visakh16

just give

OPTION (MAXRECURSION 0)

after selection statement from CTE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:09:48

[code]
;With rc1 as

( Select Base1, 1 as a From #B

Union all

Select Base1, a+1 From rc1 Where a < @RepeatShuffling

)

select * from rc1
OPTION (MAXRECURSION 0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mavershang
Posting Yak Master

111 Posts

Posted - 2010-05-12 : 12:26:36

I see. Thanks a lot. But now another problem comes out.

--------------
select * into #B from table1 --- No error

;With rc1 as

( Select Base1, 1 as a From #B

Union all

Select Base1, a+1 From rc1 Where a < @RepeatShuffling

)

--- Now error pump out saying "incorrect syntax near ')' "

But I thought it should be no problem.

quote:
Originally posted by visakh16

;With rc1 as

( Select Base1, 1 as a From #B

Union all

Select Base1, a+1 From rc1 Where a < @RepeatShuffling

)

select * from rc1
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:34:31

where's final select from CTE?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mavershang
Posting Yak Master

111 Posts

Posted - 2010-05-12 : 12:41:01

OK. So you mean there need to be a select from CTE in the statement.
Can I do this?

-----
select a, Base1, count(Base1)
from rc1 option (maxrecursion 1000)
group by a, Base1
-----

quote:
Originally posted by visakh16

where's final select from CTE?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:55:17

it should be

select a, Base1, count(Base1)
from rc1
group by a, Base1

option (maxrecursion 1000)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources