表结构：
表名：bom
zjh lbjth sx1
001 部件
001 002 部件
001 003 部件
001 004 零件
002 005 部件
002 006 零件
002 007 零件
003 008 零件
003 009 零件
005 010 部件
005 011 零件
005 012 零件
010 013 部件
010 014 零件
010 015 零件
以此类推。
想实现算法，依据最顶级图号“001”，自动提取001对应下面多有的对应数据，提取数据插入到另外一张表中。
目前这个算法只能实现2层，麻烦各位大侠帮看下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[up_getpdm_bom_new]
@stat_zjh varchar(20)
AS
begin
SET NOCOUNT ON
DECLARE @Str_lbjth varchar(20)
DECLARE @Str_lbjmc varchar(20)
DECLARE @Str_zjh varchar(20)
DECLARE @Str_sx1 varchar(20)
DECLARE @str_count int
DECLARE Structure CURSOR
FOR
SELECT lbjth,lbjmc,zjh,sx1 FROM bom where zjh = @stat_zjh
OPEN Structure
FETCH NEXT FROM Structure
INTO @Str_lbjth , @Str_lbjmc,@Str_zjh , @Str_sx1
WHILE @@FETCH_STATUS = 0
BEGIN
insert into lbjsxwh(lbjth,lbjmc,zjh,sx1)
SELECT @Str_lbjth , @Str_lbjmc,@Str_zjh , @Str_sx1
DECLARE Structure1 CURSOR
FOR
SELECT lbjth,lbjmc,zjh,sx1 FROM bom where zjh = @Str_lbjth
OPEN Structure1
FETCH NEXT FROM Structure1 INTO @Str_lbjth , @Str_lbjmc,@Str_zjh , @Str_sx1
IF @@FETCH_STATUS = 0
WHILE @@FETCH_STATUS = 0
BEGIN
print(@Str_lbjth)
insert into lbjsxwh(lbjth,lbjmc,zjh,sx1)
SELECT @Str_lbjth , @Str_lbjmc,@Str_zjh , @Str_sx1
FETCH NEXT FROM Structure1 INTO @Str_lbjth , @Str_lbjmc,@Str_zjh , @Str_sx1
END
CLOSE Structure1
DEALLOCATE Structure1
FETCH NEXT FROM Structure INTO @Str_lbjth , @Str_lbjmc,@Str_zjh , @Str_sx1
END
CLOSE Structure
DEALLOCATE Structure
RETURN
END
COMMIT TRANSACTION
SET NOCOUNT OFF
------解决方案--------------------
with c1(lbjth,sx1,lv)
as
(select lbjth,sx1,1 from bom
where zjh='001'
union all
select bom.lbjth,bom.sx1,bom.lv+1 from bom,c1 where bom.zjh=c1.lbjth
select * from c1