February 25, 2009 at 3:54 pm
I have a stored procedure in whuch I am creating a temp table which produces the following results:
menu_id.....menu_text.....parent_id.....display_seq
1..............Home............0..................1
387...........News............1..................1
393...........Features........1..................2
386...........Training.........1..................3
223...........Specials.........387..............1
395...........Testing..........386..............1
However, I want to present the data in a treeview format so I would like the results in the below order:
Home
News
Specials
Features
Training
Testing
For example, Home is at the top level because it has no parent. Next comes News because Home is its parent and it has a display sequence of 1. Even though Features comes next because it has the same parent as News, I want Specials to come next because it would fall under News.
My tree would look something like this:
Home
.....News
..........Specials
.....Features
.....Training
..........Testing
I hope this makes sense.
February 25, 2009 at 6:41 pm
Hi Rog,
I had this written up and forgot to post it before going off to lunch!
Anyway, I have a working example for you. It uses a recursive CTE. If you haven't seen these before it might be worth looking them up in BOL.
create table #menu (menu_id int, menu_text varchar(20), parent_id int, display_seq int)
insert #menu
select 1, 'Home', 0, 1
union all select 387, 'News', 1, 1
union all select 393, 'Features', 1, 2
union all select 386, 'Training', 1, 3
union all select 223, 'Specials', 387, 1
union all select 395, 'Testing', 386, 1
select * from #menu
with cte(menu_id , menu_text , parent_id , display_seq, menulevel, menuorder)
as
(
select menu_id , menu_text , parent_id , display_seq, 0 as menulevel, cast(menu_id as varchar) as menuorder
from #menu where parent_id=0
union all
select a.menu_id , a.menu_text , a.parent_id , a.display_seq, b.menulevel+1,
cast(b.menuorder + '.' + cast(a.menu_id as varchar) as varchar) as menuorder
from #menu a inner join cte b on a.parent_id = b.menu_id
)
select menu_id , menu_text , parent_id , display_seq, menulevel, menuorder from cte order by menuorder
Bevan
February 25, 2009 at 8:14 pm
Bevan -
Msg 336, Level 15, State 1, Line 11
Incorrect syntax near 'cte'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.
This is what I get when I run exactly what you had.
February 25, 2009 at 8:18 pm
The trick is that you cannot have any other select statements before a CTE. So, although the code is correct, it cannot be run all together.
Run the first part of the query (create table, insert, select *) then run the "with cte..." onwards part
Bevan
February 25, 2009 at 8:22 pm
I got it to run with no errors. I will change it up and try it with all of the real data and I'll let you know. Thanks for your help!
February 25, 2009 at 8:38 pm
The statement terminated. The maximum recursion 100 has been exhausted before statement completion
I have a pretty big table - 350 rows
February 25, 2009 at 8:47 pm
Try adding
option (maxrecursion 1000)
to the very end
Bevan
February 25, 2009 at 8:56 pm
My original result set returns 348 rows from #tmp. I am using the 'with' you suggested after my original select. I get:
(2 row(s) affected)
....
....
....
....
(348 row(s) affected)
(1 row(s) affected)
Msg 530, Level 16, State 1, Procedure rcsp_get_menu_test, Line 845
The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.
I bumped up to the maximum and still get the above message.
February 25, 2009 at 9:04 pm
Hmmm, I'm afraid you've exhausted my brain! Does anyone else have any solutions?
February 26, 2009 at 12:32 pm
I thought the CTE would do it. Anyway I have an alternative solution using loops. I am sure this is slower. So see if you could use some indexes to make it run quicker.
I have added some test data to see if the sequence of records or anything breaks this. Good luck!
=========================================================================
set nocount on
declare @min_parent_id int,
@max_parent_id int,
@index int,
@counter int,
@next_index int
if object_id('tempdb..#source') is not null
drop table #source
if object_id('tempdb..#display') is not null
drop table #display
if object_id('tempdb..#temp_source') is not null
drop table #temp_source
create table #source (menu_id int, menutext sysname, parent_id int, dseq int)
create table #display (id int, menu_id int, menutext sysname, parent_id int,dseq int)
create table #temp_source (menu_id int, menutext sysname, parent_id int,dseq int)
insert into #source values(398,'FeaturesChild1',393,1)
insert into #source values(399,'FeaturesChild2',393,2)
insert into #source values(387,'News',1, 1)
insert into #source values(1,'Home',0, 1)
insert into #source values(225,'Specials3',387,3)
insert into #source values(386,'Training',1,3)
insert into #source values(393,'Features',1,2)
insert into #source values(395,'Testing',386,1)
insert into #source values(223,'Specials',387,1)
insert into #source values(224,'Specials2',387,2)
insert into #source values(397,'Testing2',386,2)
-- Find min and max parent_ids to loop through the source table
select @min_parent_id = min(parent_id),
@max_parent_id = max(parent_id)
from #source
-- Root always has id = 1
insert into #display (id, menu_id, menutext, parent_id,dseq)
select 1, menu_id, menutext, parent_id,dseq
from #source
where parent_id = @min_parent_id
while @min_parent_id <= @max_parent_id
begin
-- Go to next minimum parent_id
select @min_parent_id = min(parent_id)
from #source
where parent_id > @min_parent_id
-- Clear previous rows
truncate table #temp_source
-- Get all leaf rows of min parent_id
insert into #temp_source(menu_id, menutext,parent_id,dseq)
select menu_id, menutext, parent_id,dseq
from #source
where parent_id = @min_parent_id
order by dseq
-- Iterate through all child rows of parent_id
select @counter = count(*),
@index = min(dseq)
from #temp_source
while @index <= @counter
begin
-- If there are no child rows in #display add the current child row next to parent
if ((select count(*) from #display where parent_id = @min_parent_id) = 0)
select @next_index = d.id + 1
from #temp_source td join #display d on td.parent_id = d.menu_id
where td.dseq = @index
else -- If there are child rows then add current child row to end of all children of parent row
select @next_index = max(d.id) + 1
from #temp_source td join #display d on td.parent_id = d.parent_id
where td.dseq = @index
-- Nudge all existing rows one position by adding 1 to id
if ((select count(*) from #display) >= @next_index)
update #display
set id = id + 1
where id > @next_index - 1
-- Add child row to the gap
insert into #display(id,menu_id, menutext,parent_id,dseq)
select @next_index, menu_id, menutext,parent_id,dseq
from #temp_source
where dseq = @index
end
end
-- Result
select * from #display order by id
========================================================================
February 26, 2009 at 1:07 pm
Rog Saber (2/25/2009)
The statement terminated. The maximum recursion 100 has been exhausted before statement completionI have a pretty big table - 350 rows
Can you post some real data using the way Bevan posted data? i.e.:
create table #menu (menu_id int, menu_text varchar(20), parent_id int, display_seq int)
insert #menu
select 1, 'Home', 0, 1
union all select 387, 'News', 1, 1
union all select 393, 'Features', 1, 2
union all select 386, 'Training', 1, 3
It will help us all give you a working and tested solution.
Cheers,
J-F
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply