Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Hierarchical data Using CTE in T-SQL 2005 Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 8:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 16, 2005 10:13 AM
Points: 4, Visits: 1
Comments posted to this topic are about the item Query Hierarchical data Using CTE in T-SQL 2005


Post #401805
Posted Thursday, October 25, 2007 2:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:47 AM
Points: 7,005, Visits: 8,451
Nice example.

... 2) The maximum number of levels is 100. This is the limitation of T-SQL. ...

This is not correct! It is the default recursion limit.
Values: 0 - 32767

You can control it by adding :

SELECT ...
FROM cte
OPTION (MAXRECURSION 150);





Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #414829
Posted Thursday, October 25, 2007 3:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
MAXRECURSION will not only restrict the recursion but also raises an error when it exceeds the specified value or the default value. You can overcome this by adding a where filter in your recursive part of the cte on level column



--Ramesh

Post #414839
Posted Thursday, October 25, 2007 3:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: Banned Members
Last Login: Tuesday, July 22, 2014 3:58 AM
Points: 2,622, Visits: 327
How to Query Hierarchical data Using CTE in T-SQL 2005? with sample codes....Try this!!!!!!!!!!!

http://www.windowsitlibrary.com/Content/2052/03/1.html
Post #414848
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse