Re: RE: Recursion in a Stored Procedure

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
>
>

MS Sql Server LazyDBA home page