Tell us what kind of result-set you are after.
Kits with all child-parts listed next to them, or ....

For example... ? Guessing ?
Kit PartCSV
K100 (3) P101, (4) P202, (1) P303

Or.. ? Guessing ?
Kit Part Qty
K100 P101 3
K100 P202 4
K100 P303 1






-----Original Message-----
From: Ralph Sabene
[mailto:mssqldba-ezmlmshield-x58036088.[Email address protected]
Sent: Friday, October 28, 2005 1:35 PM
To: LazyDBA Discussion
Subject: RE: Recursion in a Stored Procedure
Importance: Low

The table is a Bill of Materials for several different models of boats.
I want to find all records (their part number and qty) for a particular
boat model that have a specific kitcode. I changed the field names in
the below code to PARENT and CHILD to make the code easier for everyone
to read. PARENT is actually the Assembly number and CHILD is the Part
number associated with the Assembly. BTW, the BoatPartNo and Kitcode
fields in the table are also indexed.



-----Original Message-----
From: Robert Davis
[mailto:mssqldba-ezmlmshield-x52313339.[Email address protected]
Sent: Friday, October 28, 2005 12:52 PM
To: LazyDBA Discussion
Subject: RE: Recursion in a Stored Procedure

What exactly are you trying to do here.


Robert Davis


-----Original Message-----
From: Ralph Sabene
[mailto:mssqldba-ezmlmshield-x87910382.[Email address protected]
Sent: Friday, October 28, 2005 8:50 AM
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



MS Sql Server LazyDBA home page