批号分摊

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

 

 

----运行

 

--exec eyls 'SPH00000235','HWI00000002',1000

 

 

--select spid,hw,pihao,shl,sxrq  from sphwph  where  shl>0   and  spid='SPH00000235' and hw='HWI00000002'

 

 

ALTER    proc eyls @spid char(11),@hw char(11),@shl dec(14,2)

as

 

---创建临时表  为显示用

 

create table #a(

spid char(11),

hw char(11),

pihao char(40),

shl dec(14,2),

sxrq char(10)

)

 

 

 

---定义后直接赋值

---创建游标

declare   phft cursor for    

--如果不指定游标作用域，默认作用域为GLOBAL，全局变量

---不加参数，默认成forward_only,FORWARD_ONLY意味着游标只能从数据集开始向数据集结束的方向读取，FETCH NEXT是唯一的选项

---插入数据集

select spid,hw,pihao,shl,sxrq

from sphwph  where spid=@spid and hw=@hw and shl>0  order by sxrq

 

--打开游标

open phft

 

--创建游标变量

declare @sp char(11),@huow char(11),@pihao char(40),@sl dec(14,2),@sxrq  char(10)

 

fetch next from phft into @sp,@huow,@pihao,@sl,@sxrq

 

--判断

while @@fetch_status =0

 

begin

 

 

if @shl>0

begin

 

 

---创建表

declare @num dec(14,2)

select @num=@shl-(select isnull(sum(shl),0) from #a )

 

 

--插入临时表

insert into #a(spid,hw,pihao,shl,sxrq)

select spid,hw,pihao,case when shl-@num>0 then @num else shl end shl,sxrq from sphwph 

where spid=@sp and hw=@huow and pihao=@pihao and shl>0  order by sxrq

 

end

fetch next from phft into @sp,@huow,@pihao,@sl,@sxrq

end

close phft

deallocate phft

 

--显示

select * from #a

drop table #a

 

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

 

GO