Re: [CF-Database] List muppets

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

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 -<