ALTER proc [dbo].[wms_in_seek_addre_test]
@vMatno varchar(30),
@vQcstatus varchar(2),
@vBatchano varchar(20),
@vSzno varchar(20),
@vWorks varchar(10),
@vWhouse varchar(10),
@zptype varchar(10),
@zpquant int,
@vNewstore varchar(2) out, --插入库存：0：不插入；1：插入
@vNewadd varchar(2) out, --新货位：0：不是新货位；1：新货位
@vAddreno varchar(20) out, --存储区域
@vAreanopk varchar(20) out, --拣货区域
@vAreano varchar(20)out, --存储区域
@iErrorCode int out,
@iErrorText varchar(300) out --错误提示
as
declare @vZlflag varchar(5)
declare @vPutway varchar(20)
declare @vMatnomix varchar(2) --区域混品标识
declare @vBatchmix varchar(2) --区域混批标识
declare @vBattype varchar(20)
declare @vStorequant int
declare @vUpstore int
declare @vUpclstore int
declare @vBigpack int
declare @vPalquant int
declare @vaType varchar(5)
declare @iInpriority int
declare @tLtAddre table (xd varchar(5),batchcount int,emptycount int)
declare @tLtAddreBatch table (xd varchar(5),batchcount int)
declare @vXd varchar(5)
set @iErrorText = 0
set @iErrorText = ''
print 100
------省略没有问题的语句--------
print '700 ' + convert(varchar(40),getdate(),121)
--cc add 立体库寻址逻辑进行修订
if @vAreano in ('LT01','LT02')
begin
set @vNewstore = '1'
set @vNewadd = '1'
print '710 ' + convert(varchar(40),getdate(),121)
print @vAreano
print @vWhouse
print '715 ' + convert(varchar(40),getdate(),121)
/*
insert into @tLtAddre (xd,emptycount,batchcount)
select xd,sum(case when a.status = '0' and a.lockflag = 'N' and a.handlock = 'N' then 1 else 0 end),
sum(case when b.batchno is not null and b.batchno = @vBatchano and b.matno is not null and b.matno = @vMatno then 1 else 0 end)
from wms_addre a left join wms_store b
on a.whouseno = b.whouseno and a.addreno = b.addreno
where a.whouseno = @vWhouse and a.sectn = @vAreano
group by xd
*/
insert into @tLtAddre (xd,emptycount,batchcount)
select xd,COUNT(*),0
from wms_addre a
where sectn = @vAreano and whouseno = @vWhouse and status = '0' and lockflag = 'N' and handlock = 'N'
group by xd
having COUNT(*) > 0
print '720 ' + convert(varchar(40),getdate(),121)
insert into @tLtAddreBatch (xd,batchcount)
select a.xd,COUNT(distinct a.addreno)
from wms_addre a,wms_store b
where a.whouseno = b.whouseno and a.addreno = b.addreno and b.batchno = @vBatchano and b.matno = @vMatno and a.whouseno = @vWhouse and a.sectn = @vAreano
group by a.xd
print '730 ' + convert(varchar(40),getdate(),121)
update a
set a.batchcount = b.batchcount
from @tLtAddre a,@tLtAddreBatch b
where a.xd = b.xd
print '740 ' + convert(varchar(40),getdate(),121)
select top 1 @vXd = xd from @tLtAddre order by batchcount,emptycount desc
if @@rowcount = 0
begin
set @iErrorCode = -1
set @iErrorText = 'LT货位已满100'
return
end
print '750 ' + convert(varchar(40),getdate(),121)
select top 1 @vAddreno = addreno,@vAreanopk = areano
from wms_addre
where whouseno = @vWhouse and sectn = @vAreano and xd = @vXd and status = '0' and lockflag = 'N' and handlock = 'N'
if @@rowcount = 0
begin
set @iErrorCode = -1
set @iErrorText = 'LT货位已满200'
return
end
else return
end
执行该语句的时候，就停止了
insert into @tLtAddreBatch (xd,batchcount)
select a.xd,COUNT(distinct a.addreno)
from wms_addre a,wms_store b
where a.whouseno = b.whouseno and a.addreno = b.addreno and b.batchno = @vBatchano and b.matno = @vMatno and a.whouseno = @vWhouse and a.sectn = @vAreano
group by a.xd
系统执行的输出结果：
100
200
300
400
500
600
700 2017-08-14 11:52:34.097
710 2017-08-14 11:52:34.097
LT02
010
715 2017-08-14 11:52:34.097
(15 行受影响)
720 2017-08-14 11:52:34.287
语句已终止。
用户取消了查询。