GOTO 可以实现循环或者递归,语法如下:
declare @a int=0
lable:
if @a>2
return
else
begin
select @a
set @a=@a+1
goto lable
end
运行结果如下:
现有需求:通过wms对应sku的批次,找到最初入库的原始入库单号(考虑库内做过转移,批次多次改变情况)
可以使用 GOTO 实现递归查询,用法如下:
USE [wmsa]
GO
/****** Object: UserDefinedFunction [dbo].[fn_getAsnReference1] Script Date: 11/21/2018 17:38:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_getAsnReference1] (@lotnum varchar(50))
returns varchar(50)
as
begin
declare @DocNo varchar(50)= '',@result varchar(50)= ''
lable1:
select @DocNo = MAX(DocNo) from ACT_Transaction_Log
where ToLotnum = @lotnum and TransactionType = 'IN' and Status = '99'
if(ISNULL(@DocNo,'') <> '')
begin
select @result = case ASNReference1 when '' then ASNNo when null then ASNNo else ASNReference1 end
from DOC_ASN_Header
where ASNNo = @DocNo
end
else
begin
select @lotnum = FMLotNum from ACT_Transaction_Log
where ToLotnum = @lotnum and TransactionType = 'TR' and Status = '99'
and ToLotnum<>FMLotNum
goto lable1
end
return @result
end