每天生成流水码

CREATE  PROCEDURE [dbo].[WF_AUTOBH]
    @wfid  int,
    @modname  varchar(20),
    @modbh varchar(20) out
as
    SET TRANSACTION ISOLATION LEVEL  SERIALIZABLE
    BEGIN TRANSACTION
    declare
    @recont int,
    @modnum int,
    @moddate datetime,
    @moddate1 datetime,
    @month_two varchar(2),
    @day_two varchar(2),
    @ts varchar(20)


    SELECT @recont=count(*) FROM WF_BH WHERE [WFID]=@wfid and [ModName]=@modname
    if (@recont<1) 
    BEGIN
      insert WF_BH ([WFID],[ModName],[ModBH],[ModDate]) values(@wfid,@modname,0,GETDATE())
    END


    select @moddate1=getdate()
    select @moddate=moddate,@modname=modname  from WF_BH where wfid=@wfid
    if (year(@moddate1)<>year(@moddate))or(month(@moddate1)<>month(@moddate))or(day(@moddate1)<>day(@moddate))
       update WF_BH set moddate=getdate(),modbh=0 where modname=@modname
    select @modnum=modbh from WF_BH where modname=@modname
    select @modnum=1000+@modnum+1
    select @ts=convert(varchar(20),@modnum)
    select @ts=substring(@ts,2,3)
    update WF_BH set modbh=modbh+1 where modname=@modname
    
    select @month_two=convert(varchar(2),month(@moddate1))
    if(LEN(@month_two)=1)
       select @month_two='0'+@month_two
    
    select @day_two=convert(varchar(2),day(@moddate1))
    if(LEN(@day_two)=1)
       select @day_two='0'+@day_two
    select @modbh=@modname+substring(convert(varchar(4),year(@moddate1)),1,4)+@month_two+@day_two+@ts


    COMMIT TRANSACTION
    --return @modbh
   --WF_BH

GO

WF_BH  表结构WFID ModName ModBH ModDate MonthlyFlag