I am trying to create a loop that when given a part id, it will search a table of assembly parts and put all the parts into the return table. I.e. I want to explode the parts from a single part id.
It needs to be recursive because Part 1 may have parts 2, 3, 4, 5; part 3 is an assembly item with parts 9, 10; part 10 is an assembly item with parts 11, 23, 34, 31; part 11 is an assembly item with parts 23, 24.
Database is SQL Server.
I set up some sample date here: http://sqlfiddle.com/#!9/f3cc4f
I'm expecting the results:
part, level
1, 0
2, 1
3, 1
4, 1
9, 2
10, 2
11, 3
23, 3
24, 3
Below is the code I came up with. I'm hitting a Max Recursion error, even with the sample data that is only a few level. My full dataset should go no more than 15 levels. Clearly something isn't set up correctly, and I think CTE might work better.
CREATE FUNCTION [dbo].[fn_getParts] (@source_part_id int, @level int)
RETURNS @parts_list TABLE (
[part] int NOT NULL,
[level] int NOT NULL
)
AS
BEGIN
DECLARE
@max int = 0,
@cnt int = 0,
@PID int = 0,
@Plvl int = 0,
@id int = 0
DECLARE @chkParts table ([id] int identity(1,1), [PID] int, [level] int)
INSERT INTO @parts_list VALUES (@source_part_id, @level)
SET @level += 1
INSERT INTO @chkParts
SELECT [Comp_PartID], @level FROM /*visuser.[EN_BOM]*/ [Assemblies] WHERE [PartID] /*[Assembly_Part_ID]*/ = @source_part_id
SELECT @max = COUNT(*) FROM @chkParts
WHILE @cnt <= @max
BEGIN
SELECT @id = [id], @PID = [PID], @Plvl = [level] FROM @chkParts WHERE [id] = @cnt
INSERT INTO @parts_list
SELECT * FROM [fn_getParts](@PID, @Plvl)
SET @cnt += 1
END
RETURN
END
Here is sample data:
CREATE TABLE Assemblies (
PartID int NOT NULL,
Comp_PartID int NOT NULL
);
INSERT INTO Assemblies VALUES
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(3, 9),
(3, 10),
(10, 11),
(10, 23),
(10, 24),
(10, 31),
(11, 24),
(11, 23);
The following produces results which match your described logic, but are not the same as you are expecting. Maybe your logic needs a tweak?
declare @source_part_id int = 1, @level int = 0;
with cte (part, [level])
as (
select @source_part_id part, @level [level]
union all
select Comp_PartID, [level]+1
from Assemblies A
inner join cte C on C.Part = A.PartID
)
select part, [level]
from cte
order by part, [level];
Returns:
part level
1 0
2 1
3 1
4 1
5 1
6 1
9 2
10 2
11 3
23 3
24 3
31 3
24 4
23 4