Why bother with cursors or temp tables at all and just sort across Depth desc instead of Depth asc?
-Dan On Tue, Mar 29, 2011 at 2:59 PM, Jason Durham <jqdur...@gmail.com> wrote: > > Nevermind... all I could see were nails. I'll create a temp table for the > regular navigation and use that table for breadcrumbs (the depth is > accurately created on that query). > > Jason Durham > > > On Tue, Mar 29, 2011 at 1:44 PM, Jason Durham <jqdur...@gmail.com> wrote: > > > I apologize if this is an inappropriate place to ask this question. > > > > I'm using a CTE for a recursive query to assemble navigation. In the > CTE, > > I'm creating a column that contains the depth of the navigation. This > depth > > is ultimately inserted into the class of an HTML element. I'm > refactoring > > how my breadcrumbs are created and want to use a similar method. The > catch > > is, breadcrumbs start from the bottom of a recursive tree and work their > way > > up. This is causing undesired behavior on the depth calculation. The > > bottom-most element is 0. It's parent is 1. It's grandparent is 2 (and > so > > forth). I need to reverse the order of this field. > > > > I'm currently trying to use a cursor to loop back over the CTE and update > > the depth field manually. However, it appears that I'm I can't access > the > > data stored in the temporary table (CTE) from with in the cursor. Should > I > > be going about this a different way? > > > > Here is the SQL... > > > > DECLARE @THEID INT > > SET @THEID=88; > > > > DECLARE BC_Cursor CURSOR FOR > > WITH Breadcrumbs(Navigation_ID, Parent_ID, Navigation_Name, Depth) AS > > ( > > SELECT Navigation_ID, Parent_ID, Navigation_Name, 0 > > FROM Navigation > > WHERE Navigation_ID = @THEID > > > > UNION ALL > > > > SELECT n.Navigation_ID, n.Parent_ID, n.Navigation_Name, bc.Depth+1 > > FROM Navigation n > > INNER JOIN Breadcrumbs bc > > ON bc.Parent_ID = n.Navigation_ID > > ) > > SELECT * FROM Breadcrumbs OPTION (MAXRECURSION 8) > > FOR UPDATE; > > > > DECLARE @maxRows int; > > SET @maxRows = @@ROWCOUNT; <--- Will be used to renumber the depth > > > > OPEN BC_Cursor; > > FETCH NEXT FROM BC_Cursor; > > WHILE @@FETCH_STATUS = 0 > > BEGIN > > FETCH NEXT FROM BC_Cursor; <--- This prints each row. Instead > of > > outputting them, I need to update and ultimately return the whole > (virtual) > > table. > > END; > > CLOSE BC_Cursor; > > > > DEALLOCATE BC_Cursor; > > > > > > Thanks in advance. > > > > Jason Durham > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343386 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
