Solved

# Recursive function in SQL2005

Posted on 2007-10-19
Medium Priority
363 Views
I'm trying to solve the following SQL problem:
I have the  BOMTest table in the SQL Server 2005 database. I need to calculate Quantity for each part.

Example details:
Main part number A is made of parts B, E and F.  Parts B, E and F are made of the part number C. The part number C is made of the part number D. The part number B has quantity of 2.
A
|_B
|_C
|_D
|_F
|_C
|_D
|_E
|_C
|_D

Table Stucture:
********************************************
ParentPartNumber nvarchar(50)
PartNumber      nvarchar(50)
Quantity      float
Hierarchy      varchar(1000)
*******************************************
Table Data:
*******************************************************************
A      B      2      A.B
B      C      1      A.B.C
C      D      1      A.B.C.D
A      E      1      A.E
E      C      1      A.E.C
F      C      1      A.F.C
C      D      1      A.F.C.D
C      D      1      A.E.C.D
A      F      1      A.F
************************************************************************
The result of my calculation should look like this
A                   1
B      2
C      4
D      4
E      1
F      1
****************************************************
I have tried to use recursive function. However, my results are far from what I need it to be.
My SQL statement:
*****************************************************
With myTable1(ParentPartnumber, PartNumber, Quantity) as
(SELECT      distinct t1.parentpartnumber, t1.partnumber, isnull((Select  SUM(Quantity) from BOMTEST t2 Where t2.partnumber = t1.parentpartnumber),t1.quantity)AS 'Quantity'
FROM BOMTest t1
union All
Select BOMTest.parentpartnumber, BOMTest.partnumber, BOMTest.Quantity
From BOMTest  join myTable1  on myTable1.partnumber = BOMTest.Parentpartnumber)

Select  parentpartnumber, partnumber,Quantity
From myTable1
***************************************************

Any help is greatly appreciated.

Milena
0
Question by:mvoronkin

LVL 18

Accepted Solution

PFrog earned 2000 total points
ID: 20114556
Your existing table structure makes it virtually impossible to use recursive functions or CTEs, you're going to have to use a stored procedure to do this.

This should do what you want

IF OBJECT_ID('spr_BOM') IS NOT NULL
DROP PROCEDURE spr_BOM
GO

CREATE PROCEDURE spr_BOM
(@PartNumber nvarchar(50))
AS
BEGIN

CREATE TABLE #tmpBOM
(PartNumber nvarchar(50),
Quantity float,
Processed int)

INSERT INTO #tmpBOM
SELECT @PartNumber, 1, 0

DECLARE @NewCount int
SET @NewCount=1

WHILE @NewCount>0
BEGIN
UPDATE #tmpBOM SET Processed = 1 WHERE Processed = 0

INSERT INTO #tmpBOM
SELECT B.PartNumber, B.Quantity * T.Quantity, 0
FROM #tmpBOM T INNER JOIN (SELECT DISTINCT ParentPartNumber, PartNumber, Quantity FROM BOMTest) B
ON T.PartNumber = B.ParentPartNumber
WHERE T.Processed=1

UPDATE #tmpBOM SET Processed = 2 WHERE Processed = 1

SELECT @NewCount = Count(*) FROM #tmpBOM WHERE Processed = 0
END

SELECT PartNumber, Sum(Quantity) AS Quantity FROM #tmpBOM GROUP BY PartNumber

DROP TABLE #tmpBOM

END
GO

EXEC spr_BOM 'A'
0

Author Comment

ID: 20138281
Thank you very much for your help!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
###### Suggested Courses
Course of the Month17 days, 3 hours left to enroll