[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [CF-Database] recursion
For info, I also tried executing the sp in a cfquery but with the same result:
<cfquery name="selectPageBlocks" datasource="blah">
EXEC a_selectPageBlocks @pageBlockID = 0
</cfquery>
can only return 1 row
-----Original message-----
From: damian damian@greenhouse-design.co.uk
Date: Tue, 19 Oct 2004 19:00:24 +0100
To: database programming database@list.cfdeveloper.co.uk
Subject: Re: [CF-Database] recursion
> >----------------------------<
> >- see footer for list info -<
> >----------------------------<
> ....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 -<
>
> --
> 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 -<