[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [CF-Database] recursion
Damian,
I not sure about that, it might be worth googling this for "number queries returned storedproc coldfusion" or something like that.
Generally I always know the number of returns from a query...
Question, why do you need to use a cursor for that??? couldn't you just loop over the query? and excute the second SP from within CF? You see I don't understand the perf gains (not that there are any) from using a cursor over CF in this instance. Maybe someone with more knowledge of SQL server could correct me on this.
ta
Tom
----- Original Message -----
From: damian
To: database programming
Sent: Wednesday, October 20, 2004 9:03 AM
Subject: RE: [CF-Database] recursion
>----------------------------<
>- see footer for list info -<
>----------------------------<
ThxTom, but when there are x number of resultsets it's a different matter... I can't predict what will be coming out unless perhaps there is a way for SQL to output the number of queries?
....then I can loop to create my x number of resultsets
-----Original message-----
From: "Tom Smith" tomo_smith@yahoo.co.uk
Date: Tue, 19 Oct 2004 22:48:06 +0100
To: "'database programming'" database@list.cfdeveloper.co.uk
Subject: RE: [CF-Database] recursion
> >----------------------------<
> >- see footer for list info -<
> >----------------------------<
> 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 > -<
>
> --
> 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 -<