SteveEClarke (9/11/2012)

---

Hi Guys,

I am exploring the possibility of changing an inherited stored procedure to use Recursive CTE.

Basically I have 2 tables - "InventoryMaster" and "Bomstructure" - the BomStructure contains "ParentPart, Component, QtyPer, RouteNo"

The first stored procedure calls a second ;

// ------------------------

// BuildBOM1

// ------------------------

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[BuildBom1]

AS

IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'BOMTable') = 0

BEGIN

CREATE TABLE BOMTable(

TopLevel [char] (30) NULL ,

Parent_Rev [char] (10) NULL ,

Level[int] NULL ,

Assembly [char] (30) NULL ,

Component[char] (30) NULL ,

Component_Rev [char] (10) null,

QtyPer [decimal](12, 6) NULL ,

Route[char] (1) NULL

)

PRINT 'New table BOMTable added'

END

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOMTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

delete from [BOMTable]

DECLARE @StockCode CHAR(30)

DECLARE MY_CURSOR CURSOR LOCAL FOR

SELECT StockCode from InvMaster

--where

--( PartCategory = 'M' or PartCategory = 'G' ) and ComponentCount > 0 and

--StockCode not in (select distinct Component from BomStructure)

OPEN MY_CURSOR

FETCH NEXT FROM MY_CURSOR into

@StockCode

WHILE @@FETCH_STATUS = 0

BEGIN

--Route '0'

exec BuildBom2 @StockCode,@StockCode, '0' ,0

--Route '1'

exec BuildBom2 @StockCode,@StockCode, '1' ,0

--Route '2'

exec BuildBom2 @StockCode,@StockCode, '2' ,0

--Route '3'

exec BuildBom2 @StockCode,@StockCode, '3' ,0

FETCH NEXT FROM MY_CURSOR into

@StockCode

END

CLOSE MY_CURSOR

DEALLOCATE MY_CURSOR

select * from BOMTable

//------------------------------------------------------------------------------------ End of Procedure

//--------------------------------------------------

// BuildBOM2

// --------------------------------------------------

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[BuildBom2] @TOPLevel CHAR(30), @AtLevel CHAR(30), @Route CHAR(1), @Level int

AS

SET @Level = @Level + 1

DECLARE @ParentPart CHAR(30), @Component CHAR(30), @QtyPer Float

DECLARE MY_CURSOR CURSOR LOCAL FOR

SELECT ParentPart, Component, QtyPer, Route

from BomStructure

where ParentPart = @AtLevel AND

Route = @Route

OPEN MY_CURSOR

FETCH NEXT FROM MY_CURSOR into

@ParentPart, @Component, @QtyPer, @Route

WHILE @@FETCH_STATUS = 0

BEGIN

Insert into BOMTable (TopLevel, Assembly,Component,QtyPer,Route,Level)

values (@TOPLevel,@ParentPart, @Component, @QtyPer, @Route,@Level)

EXEC BuildBom2 @TOPLevel, @Component, @Route, @Level

FETCH NEXT FROM MY_CURSOR into

@ParentPart, @Component, @QtyPer,@Route

END

CLOSE MY_CURSOR

DEALLOCATE MY_CURSOR

/* End of Procedure */