根據(jù)一定原則自動(dòng)生成序列號(hào)的具體示例

需求：
    ◆1、生產(chǎn)收貨時(shí)，在數(shù)量處按CTRL+TAB，在序列號(hào)錄入處按ShiFT+F2,能自動(dòng)生成序列號(hào)；
    ◆2、生成原則如下：0703MG51T-09-S05-1200#001，其中0703為年月代碼，MG51T-09-S05-1200為物料號(hào)，#號(hào)為分隔符，001為本月第一筆收貨，以后自動(dòng)加1。
    我的語(yǔ)句如下：
    declare @t1 as varchar(32)
    declare @t2 as varchar(32)
    declare @itemcode as varchar(20)
    declare @y as varchar(20)
    declare @m as varchar(20)
    declare @ml as integer
    declare @qty1 as varchar(3)
    declare @maxid as varchar(3)
    declare @tid as varchar(3)
    select @itemcode=rtrim($[$43.5.0])
    select @y=substring(cast(year(getdate()) as varchar),3,2)
    select @m=cast(month(getdate()) as varchar)
    select @ml=len(Rtrim(@m))
    if @ml=1
    select @m=’0’+@m
    else select @m=@m
    select @t1=Ltrim(@y+@m+@itemcode)
    select @qty1=isnull(count(*),0) from OSRI where ItemCode=@itemcode and substring(IntrSerial,1,4)=@y+@m
    if Rtrim(@qty1)=’0’
    begin
    select @tid=’001’
    end
    else
    begin
    select @maxid=cast(isnull(max(cast(substring(IntrSerial,len(Rtrim(IntrSerial))-2,3) as float)),0) as float)+1 from OSRI where ItemCode=Rtrim(@itemcode) and substring(IntrSerial,1,4)=@y+@m
    if len(Rtrim(@maxid))=0
    begin
    select @tid=’001’
    end
    if len(Rtrim(@maxid))=1
    begin
    select @tid=’00’+cast(Rtrim(@maxid) as char(1))
    end
    if len(Rtrim(@maxid))=2
    begin
    select @tid=’0’+cast(Rtrim(@maxid) as char(2))
    end
    if len(Rtrim(@maxid))=3
    begin
    select @tid=cast(Rtrim(@maxid) as char(3))
    end
    end
    select @t2=@t1+’#’+@tid
    select @t2