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 |
|
Blueshadow
Starting Member
7 Posts |
 Posted - 2010-08-16 : 07:42:43
|
| Hi, I have a table that has 2 columns as Child and Parent in the following form.Child ParentA NULLB AC AD CE CF DG NULLI need to have a Stored Procedure, that gives all the children of a parent node.For example, if I pass A as the parameter to the SP, it gives the children as B and C. Then checksfor B and C in the parent columns and gives the children D and E, then checks and finds D in the parent node thus giving F.The total chidren of A are B,C,D,E and F.The total chidren of C are D,E and F.The total chidren of D are F.How can I create a SP that stores these values in a table format? |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-08-16 : 07:56:32
|
| You should look up the keywords "recursive cte". CTE means common table expression. If it's possible for you to upgrade to sql server 2008 then there is also "hierarchyid" -> http://msdn.microsoft.com/en-us/library/bb677290.aspx- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-16 : 12:50:48
|
| Depending on your requirements, you might want to look into using a materialzed path. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-16 : 13:05:32
|
| http://msdn.microsoft.com/en-us/library/ms186243.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Retrieving values of a heap structure in a table