儲位表：當USER輸入完倉庫後自動跳出，內容為目前該倉庫內所有儲位已放重量及還可以放的重量原本程式中的寫法是利用FUNCTION取得所需要的「儲位重量」及「尚可入庫重」，但開啟速度過慢，因此改良，由SQL Procedure算完後，再將內容丟回來。程式可看下面範例：
--2008071809,cODE,up_locationtable ,儲位表
use chjer
go
if exists (select name from sysobjects where name = 'up_locationtable')
drop procedure up_locationtable
go
create procedure up_locationtable (@wareno varchar(08), @userid varchar(20))
--lancelot--
as
begin
-----------------------------------------------------------------------------
declare @posino varchar(08) declare @qtyohw decimal(18,4)
declare @qtyohw1 decimal(18,4) declare @ustono varchar(20)
declare @n1 decimal(18,4)
--
declare @posi table (posino varchar(08) , qtyohw2 decimal(18,4))
declare @tmp table(wareno varchar(08) , posino varchar(08) ,
qtyohw decimal(18,4) , qtyohw1 decimal(18,4))
------------------------------------------------------------------------------
set @ustono = 'up_locationtable'
exec up_usto_delete @userid,@ustono,'','',''
------------------------------------------------------------------------------
insert into @posi
select posino, sum(isnull(qtyohw,0))
from itea
where wareno = @wareno
group by posino
order by posino
------------------------------------------------------------------------------
declare cur_posi cursor for
select posino
from posi where wareno=@wareno
open cur_posi
fetch next from cur_posi into @posino
while ( @@fetch_status <> -1 )
begin
set @qtyohw = 0
select @qtyohw=isnull(qtyohw2,0) from @posi where posino=@posino
if @posino = @wareno or @wareno = '07'
set @n1 = 99999999
else
if substring(@posino,1,1)='鐵'
set @n1 = CHJER.DBO.uf_memv_83F_n08()
else
set @n1 = CHJER.DBO.uf_memv_83F_n07()
set @qtyohw1 = @n1 - @qtyohw
insert into @tmp ( wareno , posino , qtyohw , qtyohw1 )
values (@wareno , @posino , @qtyohw , @qtyohw1 )
fetch next from cur_posi into @posino
end
close cur_posi
deallocate cur_posi
select * from @tmp where qtyohw1 > 0
end
GO
藉由暫存TABLE的方式就可以啦！
