Please ...calling SQL gurus!!

Results 1 to 2 of 2

Thread: Please ...calling SQL gurus!!

  1. #1
    Chris fromNZ Guest

    Default Please ...calling SQL gurus!!

    I need to write a stored procdeure that &#039recurses&#039 through a single table down to about 5 levels - as per Kurt M&#039s question on the &#039Stump the SQL guru&#039 section of the 4guys website. Any stored procedure code to do this would be very much appreciated

  2. #2
    Join Date
    Dec 1969

    Default RE: Please ...calling SQL gurus!!

    Here&#039s a recursive SP that I use to fix the depth of a tree once some stuff has been done to it which might invalidate the depth values. The tree table looks like this:<BR><BR>ContentTree<BR>Id<BR>oSiblingID<BR>yS iblingID<BR>ParentID<BR>ChildID<BR>Depth<BR>&#060; app specific fields&#062;<BR><BR>Basically it updates my children, then updates their children, then updates my younger sibling&#039s children, etc.<BR><BR>If Exists (Select * From sysobjects Where name = &#039TreeFixDepth&#039 And user_name(uid) = &#039dbo&#039)<BR> Drop Procedure dbo.TreeFixDepth<BR>Go<BR><BR>Create Procedure TreeFixDepth<BR><BR> (<BR> @ID int = -1,<BR> @Recursed bit = 0<BR> )<BR><BR>As <BR>Begin <BR> Declare @Next int<BR> Declare @NewDepth int<BR><BR> Select @Next = -1, @NewDepth = -1<BR> <BR> if @ID = -1<BR> Begin<BR> Select @Next = Id<BR> from ContentTree<BR> Where ParentID = -1 and oSiblingID = -1<BR> if @Next &#060;&#062; -1<BR> Execute TreeFixDepth @ID = @Next <BR> return<BR> End<BR> <BR> if @Recursed = 0<BR> Begin Transaction<BR> <BR> Select @NewDepth = Depth + 1, @Next = ChildID<BR> From contentTree<BR> Where ID = @ID<BR> If @NewDepth = -1<BR> Begin<BR> RollBack Transaction<BR> return<BR> End<BR> Update ContentTree <BR> set Depth = @NewDepth<BR> Where ParentID = @ID <BR> while @Next &#060;&#062; -1<BR> Begin<BR> Execute TreeFixDepth @ID = @Next, @Recursed = 1<BR> Select @Next = ySiblingID<BR> From ContentTree<BR> Where ID = @Next<BR> End<BR> if @Recursed = 0<BR> Commit transaction<BR>End<BR><BR><BR>return <BR> <BR><BR>Go<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts