程序在下面,希望你看了能提出宝贵的意见。千万别跟我说用别的方法替代,因为我已经有3种方法来写BOM展开了。
if exists (select * from sysobjects where name='insert_bom_proc')
drop proc insert_bom_proc
go
create proc insert_bom_proc
@partno char(20) ,
@partmodel char(20),
@name char(20),
@i int output,
@madeqty float(50)
as
begin
declare @fp char(20)
declare @fm char(20)
declare @p char(20)
declare @m char(20)
declare @l int
if @i=1
begin
select 1as nlevel,bpartno as partno,bpartmodel as partmodel,qty,ullage,
partno as fpartno,partmodel as fpartmodel,delsig into #bomlevel
from bom
where partno=@partno and partmodel=@partmodel
select 1as nlevel,bpartno as partno,bpartmodel as partmodel,qty*@madeqty as qty,ullage,
partno as fpartno,partmodel as fpartmodel,delsig,'1' as isbomerror into #result
from bom
where partno=@partno and partmodel=@partmodel and delsig=1
end
DECLARE bom_cursor CURSOR FOR
SELECT nlevel,partno,partmodel,fpartno,fpartmodel FROM #bomlevel where nlevel=@i and delsig=0
OPEN bom_Cursor
FETCH NEXT FROM bom_cursor
INTO @l, @p,@m,@fp,@fm
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #bomlevel
select @i+1,bpartno,bpartmodel,
qty*(select top 1 isnull(qty,1) from bom where partno=@fp and partmodel=@fm and bpartno=@p and bpartmodel=@m),
ullage,partno,partmodel,delsig from bom where partno=@p and partmodel=@m
if not exists(select 1 from bom where partno=@p and partmodel=@m)
begin
insert into #result
select nlevel,partno,partmodel,qty*@madeqty as qty,ullage,fpartno,fpartmodel,delsig,'0' as isbomerror
from #bomlevel where partno=@p and partmodel=@m
end insert into #result
select @i+1,bpartno,bpartmodel,
qty*@madeqty*(select top 1 isnull(qty,1) from bom where partno=@fp and partmodel=@fm and bpartno=@p and bpartmodel=@m),
ullage,partno,partmodel,delsig,'1' as isbomerror from bom where partno=@p and partmodel=@m and delsig=1
FETCH NEXT FROM bom_cursor
INTO @l, @p,@m,@fp,@fm
end
CLOSE bom_cursor
DEALLOCATE bom_cursor
if @i<32
begin
set @i=@i+1
exec @name @p,@m,@name,@i,@madeqty
end
else
begin
select a.partno,a.partmodel,sum(a.qty) as qty,sum(a.ullage) as ullage,a.isbomerror
from #result a,item b
where b.partno=a.partno
and b.partmodel=a.partmodel
and b.isuse=1
group by a.partno,a.partmodel,a.isbomerror
end
end
go
if exists (select * from sysobjects where name='insert_bom_proc')
drop proc insert_bom_proc
go
create proc insert_bom_proc
@partno char(20) ,
@partmodel char(20),
@name char(20),
@i int output,
@madeqty float(50)
as
begin
declare @fp char(20)
declare @fm char(20)
declare @p char(20)
declare @m char(20)
declare @l int
if @i=1
begin
select 1as nlevel,bpartno as partno,bpartmodel as partmodel,qty,ullage,
partno as fpartno,partmodel as fpartmodel,delsig into #bomlevel
from bom
where partno=@partno and partmodel=@partmodel
select 1as nlevel,bpartno as partno,bpartmodel as partmodel,qty*@madeqty as qty,ullage,
partno as fpartno,partmodel as fpartmodel,delsig,'1' as isbomerror into #result
from bom
where partno=@partno and partmodel=@partmodel and delsig=1
end
DECLARE bom_cursor CURSOR FOR
SELECT nlevel,partno,partmodel,fpartno,fpartmodel FROM #bomlevel where nlevel=@i and delsig=0
OPEN bom_Cursor
FETCH NEXT FROM bom_cursor
INTO @l, @p,@m,@fp,@fm
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #bomlevel
select @i+1,bpartno,bpartmodel,
qty*(select top 1 isnull(qty,1) from bom where partno=@fp and partmodel=@fm and bpartno=@p and bpartmodel=@m),
ullage,partno,partmodel,delsig from bom where partno=@p and partmodel=@m
if not exists(select 1 from bom where partno=@p and partmodel=@m)
begin
insert into #result
select nlevel,partno,partmodel,qty*@madeqty as qty,ullage,fpartno,fpartmodel,delsig,'0' as isbomerror
from #bomlevel where partno=@p and partmodel=@m
end insert into #result
select @i+1,bpartno,bpartmodel,
qty*@madeqty*(select top 1 isnull(qty,1) from bom where partno=@fp and partmodel=@fm and bpartno=@p and bpartmodel=@m),
ullage,partno,partmodel,delsig,'1' as isbomerror from bom where partno=@p and partmodel=@m and delsig=1
FETCH NEXT FROM bom_cursor
INTO @l, @p,@m,@fp,@fm
end
CLOSE bom_cursor
DEALLOCATE bom_cursor
if @i<32
begin
set @i=@i+1
exec @name @p,@m,@name,@i,@madeqty
end
else
begin
select a.partno,a.partmodel,sum(a.qty) as qty,sum(a.ullage) as ullage,a.isbomerror
from #result a,item b
where b.partno=a.partno
and b.partmodel=a.partmodel
and b.isuse=1
group by a.partno,a.partmodel,a.isbomerror
end
end
go
解决方案 »
- 如何实现Draging的时候我按下Shift键,Drag图标会变化,
- 怎样改变程序中的鼠标指针?
- delphi发布程序时候可以选择需要附带的组件,是在那设置啊?
- 我在D7里面写一个程序,要用到ftp,大家推荐一个好一点的ftp控件吧?
- 不知道怎么了!
- 哪位大侠知道“实现HTML可视化编辑”以及像Delphi等IDE那样的“属性编辑器”用什么控件可以实现??谢谢!
- 关于ListBox的小问题
- 动态报表设计
- 如何在改变DBGrid中当前点击选中的单元格的背景色?
- Delphi 窗体变量变为nil 的问题
- 如何实现在dbgrid中自动求和?
- 我需要delphi的安装程序文件vcl40.bpl,或delphi4的完整安装程序installshield?
现在的sql server只能支持32次调用!
以前的更差!只能支持16次!
应该不是你的程序问题,可以到 SQL Server 版块去问问那里的专家。