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

MS Sql Server LazyDBA home page