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



MS Sql Server LazyDBA home page