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

MS Sql Server LazyDBA home page