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 start
the 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 comment
on whether taking almost 1 second for creating a relatively simple
cursor 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, which
will 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



MS Sql Server LazyDBA home page