[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
RE: [CF-Database] recursion
You need to user cfstoredproc and cfprocresult
The result sets that you output (name) are in the order that the cursor
throws them out, so you'd need to set the result set - otherwise it'll only
send back the first one.
You'd end up with something like the following:
<cfstoredproc procedure="a_selectPageBlocks" datasource="#ds#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER"
variable="@pageblockID" value="0" null="No">
<cfprocresult name="results1" resultset="1">
<cfprocresult name="results2" resultset="2">
<cfprocresult name="results3" resultset="3">
<cfprocresult name="results4" resultset="4">
<cfprocresult name="results5" resultset="5">
</cfstoredproc>
-----Original Message-----
From: database-bounces@list.cfdeveloper.co.uk
[mailto:database-bounces@list.cfdeveloper.co.uk] On Behalf Of damian
Sent: 19 October 2004 22:20
To: database programming
Subject: Re: [CF-Database] recursion
>----------------------------<
>- see footer for list info -<
>----------------------------<
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
-<
--
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 -<