Help! Can I control the recursion times of a recursive CTE?

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

Sorry, wrong format in last post. Here's the question:

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

|||Check out maxrecursion here http://msdn2.microsoft.com/en-us/library/ms175972.aspx|||Thanks. I tried it but got some error:

CREATE TABLE Table1 ( fr int, t int)
INSERT Table1 VALUES (1, 2)
INSERT Table1 VALUES (2, 3)
INSERT Table1 VALUES (3, 4)
INSERT Table1 VALUES (1, 3)
INSERT Table1 VALUES (1, 4)
INSERT Table1 VALUES (4, 1)
WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
OPTION (MAXRECURSION 1000)
)

It says Incorrect syntax near the keyword 'OPTION'. What's wrong with it?
Thanks!
|||

You need to use maxrecursion in the projection out of the CTE, the below works for me in SP1;

use tempdb

go

CREATE TABLE Table1 ( fr int, t int)

go

INSERT Table1 VALUES (1, 2)

INSERT Table1 VALUES (2, 3)

INSERT Table1 VALUES (3, 4)

INSERT Table1 VALUES (1, 3)

INSERT Table1 VALUES (1, 4)

INSERT Table1 VALUES (4, 1)

go

WITH CTE_Sample (fr, t, level) AS

(

SELECT Table1.fr, Table1.t, 1 AS level

FROM Table1

WHERE fr=1

UNION ALL

SELECT Table1.fr, Table1.t, level+1

FROM Table1

INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t

)

select *

from CTE_Sample

OPTION (MAXRECURSION 2);

go

|||

Thank you Euan! It works!

btw. Do you know if I can join CTE_Sample with other tables or other CTEs? I tried but got some errors.

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
)

WITH CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length

It says "Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
" Then I added a semicolon at the end of CTE_Sample, then it says "Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ';'.
".

What should I do?

|||

I'm not 100% clear on what yuo are actually trying to do but in principle you can use CTEs very flexibly, I suggest reviewing Boks On Line and doing some online searching for them, there are lots of examples.

You can also try looking in the T-SQL forum for examples in there.

|||

You should separate the With statements with a comma and remove the second "with".

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
) ,

CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length