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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-20 : 09:15:01
|
Vinay writes "Hi EB, I am facing a problem due to the limitation of 32 nested(recusrsive) calls of the Stored Procedure with the Microsft SQL Server7. How to get rid of that? I am writing a Stored Procedure which will extract all the childrens(can go down to any level successively) for the given Parent. Let me explain the scenario in more details....There is a Table say, TEST which represents hierarchical relationship. It has two columns ID and PARENT_ID. (PARENT_ID is referencing to the 'ID' column of the same table) TEST ID PARENT_ID 1 null 2 1 3 1 4 2 5 2 6 5 This table represents the following hierarchy :- 1 / 2 3 / 4 5 6 This heirarchy can go upto any level. When I give the Parent_Id as 1, it should give me all successive child nodes, i.e. the result should be,23456.... and so on (if any)I able to write a stored procedure which work on recurssion technique and can give me such type of result. But as SQL server has limitation of 32 recurssive calls, it fails when the above tree/heirarchy has more than 32 levels. The requirement is that there should not any such limit on levels, and I should be able to get all the levels below the given parent level.Can anybody help me out in this regard? How to get rid of that limitation? OR is there any other way to get the same kind of resultset without using recurssion / stored procedure itself. In Oracle there is one way of using "start with - connect by prior" clause. Is there something similar to this in Microsoft SQL server?Thanks and regards,Vinay Joshi." |
|
|
|
|
|
How to get rid of the limit of 32 recurssive calls in MS SQL Server 7