我认为不能讲这个数据库设计的不合理,ERP中的BOM不就这样的呢,我来帮你UP
下面是一段BOM分解的代码,你修改以下就可以实现你所须的功能了
if exists(select * from sysobjects where type='p' and name='spp_call_back')
drop proc spp_call_back
go
create proc spp_call_back @index int,@wlbh nvarchar(20),
@sl numeric(9,3),@rq datetime as
begin
declare @sql varchar(1000)
select @sql = ''
select @sql = @sql + ' declare @CurRq datetime'
select @sql = @sql + ' select @CurRq = dateadd(dd,-1,''' + convert(varchar(10),@rq,120) + ''')'
select @sql = @sql + ' declare @CurBH nvarchar(20),@CurSL numeric(9,3)'
select @sql = @sql + ' declare plan_cursor' + convert(varchar(5),@index) + ' cursor for select distinct zitemid,fzsum*' + convert(varchar(20),@sl) + ' from work_bom where fitemid='''+@wlbh+''' and fzkind=''A'''
select @sql = @sql + ' open plan_cursor' + convert(varchar(5),@index)
select @sql = @sql + ' fetch next from plan_cursor'+convert(varchar(5),@index)+' into @CurBH,@CurSL'
select @sql = @sql + ' while @@fetch_status = 0'
select @sql = @sql + ' begin'
select @sql = @sql + ' insert into #temp(f_level,f_bh,f_sl,f_rq) values(' + convert(varchar(5),@index) + ',@CurBH,@CurSL,@CurRq)'
select @sql = @sql + ' exec spp_call_back ' + convert(varchar(5),@index+1)+',@CurBH,@CurSL,@CurRq'
select @sql = @sql + ' fetch next from plan_cursor' + convert(varchar(5),@index) +' into @CurBH,@CurSL'
select @sql = @sql + ' end'
select @sql = @sql + ' close plan_cursor' + convert(varchar(5),@index)
select @sql = @sql + ' deallocate plan_cursor' + convert(varchar(5),@index)
exec(@sql)
end
go
if exists(select * from sysobjects where type='p' and name='spp_main')
drop proc spp_main
go
create proc spp_main @topbh nvarchar(20),@sl numeric(9,3),@rq datetime as
begin
create table #temp
(
f_id int IDENTITY primary key,
f_level int,
f_bh nvarchar(20),
f_sl numeric(9,3),
f_rq varchar(10),
)
exec spp_call_back 1, @topbh, @sl, @rq
select * from #temp
end;
go
spp_main 'CHACHE01',1,'2002-07-25'