Re: [CF-Database] recursion

[damian <damian@greenhouse-design.co.uk>]

...and I went and left the email subject as "List muppets", what does that tell you!

-----Original message-----
From: damian damian@greenhouse-design.co.uk
Date: Tue, 19 Oct 2004 18:55:58 +0100
To: database programming database@list.cfdeveloper.co.uk
Subject: Re: [CF-Database] List muppets

>  >----------------------------<
>  >- see footer for list info -<
>  >----------------------------<
>  A muppet asks ;)
>  
>  I got a wee recursive stored proc here which pulls out 6 (individual) rows from my db in Query Analyser:
>  
>  --------------------------------------------------
>  CREATE PROCEDURE [dbo].[a_selectPageBlocks]
>  	@pageBlockID	int
>  AS
>  
>  DECLARE @parentID int
>  DECLARE @pageBlockTitle varchar(500)
>  DECLARE cur_Level CURSOR LOCAL FOR
>  
>  	SELECT parentID, pageBlockID, pageBlockTitle
>  	FROM c_pageBlocks
>   	WHERE parentID = @pageBlockID
>  	ORDER BY listPosition
>  
>  OPEN cur_Level
>  
>  	FETCH NEXT FROM cur_Level INTO @parentID, @pageBlockID, @pageBlockTitle
>  	WHILE @@FETCH_STATUS = 0
>  	BEGIN
>  		SELECT @parentID AS parentID, @pageBlockID AS pageBlockID, @pageBlockTitle AS pageBlockTitle
>  		EXEC a_selectPageBlocks @pageBlockID
>  		FETCH NEXT FROM cur_Level INTO @parentID, @pageBlockID, @pageBlockTitle
>  	END
>  CLOSE cur_Level
>  DEALLOCATE cur_Level
>  GO
>  --------------------------------------------------
>  
>  ok, so then I call it with:
>  
>  <cfstoredproc datasource="blahblah" procedure="a_selectPageBlocks">
>  	<cfprocparam cfsqltype="CF_SQL_INTEGER" dbvarname="@i_pageBlockID" type="in" value="0">
>  	<cfprocresult name="selectPageBlocks">
>  </cfstoredproc>
>  
>  Thing is, it only outputs one row in cfoutput or dump, presumably because there is a new query for each recursion in the sp... anyone know how to get round this? (compile a new query from the results of the recursive ones in the SP??)
>  
>  Cheers
>  d
>  
>  
>  
>  -----Original message-----
>  From: "Russ Michaels \(Snake\)" snake.lists@snakepit.net
>  Date: Tue, 19 Oct 2004 15:43:08 +0100
>  To: "'database programming'" database@list.cfdeveloper.co.uk
>  Subject: [CF-Database] List muppets
>  
>  >  >----------------------------<
>  >  >- see footer for list info -<
>  >  >----------------------------<
>  >  Nope, I decided to make a bit more blatant for Muppets like this.
>  >  
>  >  > 
>  >  > And here was me about to email Russ to ask him if would 
>  >  > remove the "see footer for list info" because its beginning 
>  >  > to get on my nerves.  Guess it'll have to stay on there for a 
>  >  > while longer yet :(
>  >  > 
>  >  
>  >  
>  >  --
>  >  For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo
>  >  
>  >  --
>  >  CFDeveloper Sponsors:-
>  >  >- Hosting provided by www.cfmxhosting.co.uk -<
>  >  >- Forum provided by www.fusetalk.com -<
>  >  >- DHTML Menus provided by www.APYCOM.com -<
>  >  >- Lists hosted by www.Gradwell.com -<
>  >  >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
>  
>  --
>  For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo
>  
>  --
>  CFDeveloper Sponsors:-
>  >- Hosting provided by www.cfmxhosting.co.uk -<
>  >- Forum provided by www.fusetalk.com -<
>  >- DHTML Menus provided by www.APYCOM.com -<
>  >- Lists hosted by www.Gradwell.com -<
>  >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<