Re: RE: RE: Recursion in a Stored Procedure

Re: RE: RE: Recursion in a Stored Procedure

 

  

You're very welcome. A general rule of thumb is to avoid cursors if at
all possible.

----- Original Message -----
From: Ralph Sabene <mssqldba-ezmlmshield-
x70119759.[Email address protected]
Date: Monday, October 31, 2005 10:13 am
Subject: RE: RE: Recursion in a Stored Procedure

> Trevor,
>
> OUTSTANDING!! That's exactly what I needed. It runs in less than a
> second. I didn't know how resource intensive using a cursor could be.
> I've created stored procedures with a cursor before, but never
> where a
> cursor is created for each recursive call.
>
> Thanks again for taking the time to provide me with a solution. I
> reallyappreciate it.
>
> Ralph
>
>
>
> -----Original Message-----
> From: tweir
> [mailto:mssqldba-ezmlmshield-x73531574.[Email address protected]
> Sent: Monday, October 31, 2005 8:53 AM
> To: LazyDBA Discussion
> Subject: Re: RE: Recursion in a Stored Procedure
>
> that wasn't quite right - need to stop the loop somehow. This may
> work
> a little better:
>
> DECLARE @firstin int,@rows int
> DECLARE @tmprecs TABLE(
> recid int identity (1,1) PRIMARY KEY,
> PARENT char(6),
> CHILD char(6)
> Qty decimal(18,2)
> Kitcode2 char(4))
>
> Insert @tmprecs (PARENT,CHILD,Qty,Kitcode2)
> SELECT tBOM.PARENT, tBOM.CHILD, tBOM.Qty, tBOM.Kitcode
> FROM dbo.tBOM tBOM
> WHERE tBOM.BoatPartNo = @BoatModel AND tBOM.PARENT = @PARENT
> AND tBOM.Kitcode = @KitCode
>
> set @rows = @@ROWCOUNT
> set @firstin = 0
>
> WHILE @rows > 0 BEGIN
> Insert @tmprecs (PARENT,CHILD,Qty,Kitcode2)
> SELECT child_tBOM.PARENT,
> child_tBOM.CHILD,
> child_tBOM.Qty,
> child_tBOM.Kitcode
> FROM @tmprecs parent_tBOM
> JOIN dbo.tBOM child_tBOM
> ON parent_tBOM.CHILD = child_tBOM.PARENT
> where parent_tBOM.reci > @firstin
> set @rows = @@rowcount
> set @firstin = SCOPE_IDENTITY() - @rows
> END
>
> SELECT * from @tmprecs
>
>
> ----- Original Message -----
> From: tweir <mssqldba-ezmlmshield-x95514371.[Email address protected]
> Date: Monday, October 31, 2005 8:40 am
> Subject: Re: RE: Recursion in a Stored Procedure
>
> > Perhaps something like this?
> >
> > DECLARE @tmprecs TABLE(
> > PARENT2 char(6),
> > CHILD char(6)
> > Qty decimal(18,2)
> > Kitcode2 char(4))
> >
> > Insert @tmprecs
> > SELECT tBOM.PARENT, tBOM.CHILD, tBOM.Qty, tBOM.Kitcode
> > FROM dbo.tBOM tBOM
> > WHERE tBOM.BoatPartNo = @BoatModel AND tBOM.PARENT = @PARENT
> > AND tBOM.Kitcode = @KitCode
> >
> > WHILE @@ROWCOUNT = 0
> > BEGIN
> > Insert @tmprecs
> > SELECT child_tBOM.PARENT,
> > child_tBOM.CHILD,
> > child_tBOM.Qty,
> > child_tBOM.Kitcode
> > FROM @tmprecs parent_tBOM
> > JOIN dbo.tBOM child_tBOM
> > ON parent_tBOM.CHILD = child_tBOM.PARENT
> >
> > END
> >
> > SELECT * from @tmprecs
> >
> > ----- Original Message -----
> > From: Ralph Sabene <mssqldba-ezmlmshield-
> > x53878665.[Email address protected]
> > Date: Monday, October 31, 2005 8:18 am
> > Subject: RE: Recursion in a Stored Procedure
> >
> > > Hi,
> > >
> > > Thanks for your reply. No, it's not in an infinite loop. After
> > > each call
> > > to the stored procedure, the next call is using the value of
> the
> > child> record. The recursion is definitely working correctly
> > because if I
> > > startthe first call several layers into the hierarchy, I do
> get
> > > all the
> > > records back. However, it's taking about 23 seconds to make
> > about 31
> > > calls to the stored procedure via the recursion.
> > >
> > > I've never done a recursive stored procedure before. Can
> anyone
> > > commenton whether taking almost 1 second for creating a
> > relatively
> > > simplecursor is normal? Does anyone see an alternative to
> doing
> > it
> > > this way?
> > >
> > > Thanks for any help.
> > >
> > >
> > > -----Original Message-----
> > > From: noble mudenha
> > > [mailto:mssqldba-ezmlmshield-x14338030.[Email address
> protected]
> > > Sent: Friday, October 28, 2005 12:14 PM
> > > To: LazyDBA Discussion
> > > Subject: RE: Recursion in a Stored Procedure
> > >
> > > Looks like an infinite loop to me, if it matches, it calls
> > itself,
> > > whichwill produce a match, etc etc.
> > > Are you trying to get the child and quantity for a kit for a
> > boat
> > > model?
> > > I'm probably missing the point, but why not simply use and
> > > kitcode=@kitcode
> > > in where statement?
> > >
> > >
> > > -----Original Message-----
> > > From: Ralph Sabene
> > > [mailto:mssqldba-ezmlmshield-x87910382.[Email address
> protected]
> > > Sent: Friday, October 28, 2005 5:50 PM
> > > To: LazyDBA Discussion
> > > Subject: Recursion in a Stored Procedure
> > >
> > >
> > > Hello All,
> > >
> > > Here's your chance to beat up on the developer! I'm trying to
> > > write a
> > > recursive stored procedure against a single table of about
> 200K
> > > records.The code is working, based on print statements that
> I'm
> > > using to see it
> > > while it's working, but it is taking forever to walk through the
> > > records. I'm killing it after 30 minutes or so. The PARENT and
> CHILD> > fields are defined as char(6) and they're both indexed.
> The
> > table
> > > has a
> > > primary key field, ID, defined. What am I missing here? Thanks
> > for any
> > > help.
> > >
> > >
> > >
> > > CREATE PROCEDURE [dbo].[spGetKitParts]
> > > @BoatModel char(6),
> > > @PARENT char(6),
> > > @KitCode char(4)
> > > AS
> > >
> > > DECLARE @PARENT2 char(6)
> > > DECLARE @CHILD char(6)
> > > DECLARE @Qty decimal(18,2)
> > > DECLARE @Kitcode2 char(4)
> > >
> > > DECLARE cur_GetKitParts CURSOR LOCAL FOR --create cursor
> > >
> > > SELECT dbo.tBOM.PARENT, dbo.tBOM.CHILD, dbo.tBOM.Qty,
> > > dbo.tBOM.Kitcode
> > > FROM dbo.tBOM
> > > WHERE dbo.tBOM.BoatPartNo = @BoatModel AND dbo.tBOM.PARENT =
> > > @PARENT
> > > ORDER BY dbo.tBOM.CHILD
> > >
> > > OPEN cur_GetKitParts
> > >
> > > FETCH NEXT FROM cur_GetKitParts INTO @PARENT2, @CHILD, @Qty,
> > > @Kitcode2
> > >
> > > WHILE @@FETCH_STATUS = 0 --cycle through each record
> > > BEGIN
> > > IF @Kitcode2=@Kitcode --matching kitcode found
> > > SELECT @CHILD, @Qty
> > > EXEC spGetKitParts @BoatModel, @CHILD, @Kitcode
> > > FETCH NEXT FROM cur_GetKitParts INTO @PARENT2,
> > > @CHILD, @Qty, @Kitcode2
> > > END
> > >
> > > CLOSE cur_GetKitParts
> > > DEALLOCATE cur_GetKitParts
> > > GO
> > >
> > >
> > > ---------------------------------------------------------------
> --
> > --
> > > --
> > > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > > Website : http://www.LazyDBA.com
> > > To unsubscribe: http://www.lazydba.com/unsubscribe.html
> > >
> > >
> > >
> > >
> > > ---------------------------------------------------------------
> --
> > --
> > > --
> > > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > > Website : http://www.LazyDBA.com
> > > To unsubscribe: http://www.lazydba.com/unsubscribe.html
> > >
> > >
> > >
> > >
> > > ---------------------------------------------------------------
> --
> > --
> > > --
> > > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > > Website : http://www.LazyDBA.com
> > > To unsubscribe: http://www.lazydba.com/unsubscribe.html
> > >
> > >
> >
> >
> > -----------------------------------------------------------------
> --
> > --
> > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > Website : http://www.LazyDBA.com
> > To unsubscribe: http://www.lazydba.com/unsubscribe.html
> >
> >
>
>
> -------------------------------------------------------------------
> --
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> -------------------------------------------------------------------
> --
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>

MS Sql Server LazyDBA home page