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 |
|
javaisok
Starting Member
11 Posts |
 Posted - 2003-10-15 : 04:19:33
|
| this is recursive procedure that populates all childrens for the given node into a temporaly table. it does working but why on each iteration when there are no more childrens for the current node select statement populates empty row into the result recordset? this breaks my client software. CREATE PROCEDURE SP_GET_CHILDRENS @parent_id int -- node indexAS declare @vt_id int -- current node index declare cr_get_children cursor local read_only for select article_group_id from dbo.article_groups where fk_article_group_id = @parent_id and article_group_id <> fk_article_group_id -- this select makes all my problems -- if not exists (select top 1 entry_id from #temp) insert into #temp values (@parent_id) -- open cr_get_children fetch next from cr_get_children into @vt_id while @@fetch_status = 0 begin insert into #temp values (@vt_id) exec SP_GET_CHILDRENS @parent_id = @vt_id fetch next from cr_get_children into @vt_id end close cr_get_children deallocate cr_get_children |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-15 : 08:22:26
|
| Don't use cursors. Take a look at this article:http://www.sqlteam.com/item.asp?ItemID=8866To find all of the children of a node (i.e. EmployeeID=1234) use the following:SELECT * FROM Tree WHERE Lineage Like '/' + (SELECT CAST(NodeID as varchar) FROM Tree WHERE EmployeeID=1234) + '/' |
 |
|
|
|
|
|
empty rows on the top of result recodrset