//多物料进仓存储过程
create procedure dwljc
@m_iomark char(1),
@m_stat varchar(30) output,
@m_msg varchar(10) output
as
declare
@jcdh char(10),
@wldm char(10),
@jcnum int,
@jcrq datetime,
@bz1 char(100),
@dw char(4),
@czrydm char(10)
begin transaction
set @jcdh=(select top 1 jcdh from xg070416wljcc order by substring(jcdh,2,9) desc);
if @jcdh is null
set @jcdh='s100000000';
set @jcdh=substring(@jcdh,2,9);
set @jcdh=convert(varchar(10),(convert(int,@jcdh)+1));
set @jcdh='s'+@jcdh;
declare dwl_cursor cursor for
select 日期,物料编号,人员编号,进仓数量,单位,备注 from #temptable
open dwl_cursor
fetch next from dwl_cursor into @jcrq,@wldm,@czrydm,@jcnum,@dw,@bz1
while (@@fetch_status=0)
begin
if exists(select wlno from xg070416wlb with(tablockx) where wlno=@wldm )
update xg070416wlb with(tablockx) set kcnum=kcnum+@jcnum where wlno=@wldm;
else
begin
select @m_stat=@wldm+'物料不存在!请先添加物料!!'
select @m_msg='无'
rollback transaction
return
end
insert into xg070416wljcc(jcdh,jcrq,czrydm,wldm,m_iomark,jcnum,dw,bz) values(@jcdh,@jcrq,@czrydm,@wldm,'进仓',@jcnum,@dw,@bz1)
fetch next from dwl_cursor into @jcrq,@wldm,@czrydm,@jcnum,@dw,@bz1
end
close dwl_cursor ---关闭游标
deallocate dwl_cursor
select @m_stat='操作成功!'
select @m_msg=@jcdh
commit transaction
go