RE: [CF-Database] List muppets

["Russ Michaels \(Snake\)" <snake.lists@snakepit.net>]

 Having never done a cursor, I will use a bit of deduction, but I suspect
you have multiple result sets. And you can return return multiple result
sets with a cfstoredproc, you need to declare the result sets with your
cfprocparam, but you will have to check the manual for that as I do not
remember how :-)


> -----Original Message-----
> From: database-bounces@list.cfdeveloper.co.uk 
> [mailto:database-bounces@list.cfdeveloper.co.uk] On Behalf Of damian
> Sent: 19 October 2004 18:56
> To: database programming
> 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