SQL 生成可配置流水号

需求背景每执行一次方法，根据公式返回最新的流水号。第一次使用时需要先插入一条数据，BizSeqValue 为流水起始号：A2014030000，Formula 为公式：A[yyyy][mm][c4]，UseTime 为当前时间。

创建流水号表CREATE TABLE [dbo].[SM_BizSeqNo](    [BizSeqID] [int] IDENTITY(1,1) NOT NULL,    [BizSeqValue][nvarchar](50) NULL,    [BizSeqName] [nvarchar](50) NULL,[UseTime] [datetime] NULL,    [Formula] [varchar](50) NULL)

创建PadLeft 函数Create function [dbo].[PadLeft](@num varchar(16),@paddingChar char(1),@totalWidth int)returns varchar(16) asbeginif(len(@num)=0)begin    return ''enddeclare @curStr varchar(16)select @curStr = isnull(replicate(@paddingChar,@totalWidth - len(isnull(@num ,0))), '') + @numreturn @curStrend

Create PROCEDURE [dbo].[Biz_GetSeqNo]    @BizSeqType varchar(50)ASBEGIN    declare@BizSeqValue varchar(50),@Prefix varchar(10),@Year varchar(4),@Yearindex int,@Month varchar(2),@Monthindex int,@Day varchar(2),@Dayindex int,@DigitsIndex int,@DigitsEndIndex int,@Digits int,@FlowNum varchar(50),@Half1 varchar(50),@Half2 varchar(50),@Count int,@Formula varchar(50)set @Year='' set @Month='' set @Day=''select @Formula=Formula,@BizSeqValue=BizSeqValue from SM_BizSeqNo where BizSeqType=@BizSeqTypeselect @Prefix=SUBSTRING(@Formula,0,charindex('[',@Formula))select @DigitsIndex=charindex('[c',@Formula)select @DigitsEndIndex=charindex(']',@Formula,@DigitsIndex+2)select @Digits=SUBSTRING(@Formula,@DigitsIndex+2,@DigitsEndIndex-@DigitsIndex-2)select @Yearindex=charindex('[yyyy]',@Formula)if(@Yearindex>0)begin    select @year=YEAR(getdate())endelse if(charindex('[yy]',@Formula)>0)begin    select @Yearindex=charindex('[yy]',@Formula)    select @year=SUBSTRING(CAST(YEAR(getdate()) as varCHAR(4)),3,2)endselect @monthindex=charindex('[mm]',@Formula)if(@monthindex>0)begin    select @month=month(getdate())endselect @dayindex=charindex('[dd]',@Formula)if(@dayindex>0)begin    select @day=day(getdate())endselect @Half1=@Prefix+@Year+dbo.PadLeft(@Month,'0',2)+dbo.PadLeft(@Day,'0',2)--select @Half1,@Prefix,@Yearif(@Dayindex>0)begin    select @Half2=SUBSTRING(@Formula,@Dayindex+4,999)    select @Count=COUNT(1) from SM_BizSeqNo where BizSeqType=@BizSeqType and DATEDIFF(DAY,UseTime,GETDATE())=0     select @DigitsIndex=@DigitsIndex-6endelse if(@Monthindex>0)begin    select @Half2=SUBSTRING(@Formula,@Monthindex+4,999)    select @Count=COUNT(1) from SM_BizSeqNo where BizSeqType=@BizSeqType and DATEDIFF(MONTH,UseTime,GETDATE())=0     select @DigitsIndex=@DigitsIndex-4endelse if(@Yearindex>0)begin    select @Half2=SUBSTRING(@Formula,@Yearindex+2+LEN(@year),999)    select @Count=COUNT(1) from SM_BizSeqNo where BizSeqType=@BizSeqType and DATEDIFF(YEAR,UseTime,GETDATE())=0     select @DigitsIndex=@DigitsIndex-2endelsebegin    select @Half2=SUBSTRING(@Formula,LEN(@Prefix)+1,999)    select @Count=COUNT(1) from SM_BizSeqNo where BizSeqType=@BizSeqTypeendif(@Count>0) --当前流水号+1begin    select @FlowNum=cast(SUBSTRING(@BizSeqValue,@DigitsIndex,@Digits) as bigint)+1    select @FlowNum=dbo.PadLeft(@FlowNum,'0',@Digits)    update SM_BizSeqNo set BizSeqValue=@Half1+REPLACE(@Half2,'[c'+cast(@Digits as varchar(50))+']',@FlowNum),    UseTime = GETDATE() where BizSeqType=@BizSeqTypeendelsebegin    select @FlowNum=dbo.PadLeft('1','0',@Digits)    update SM_BizSeqNo set BizSeqValue=@Half1+REPLACE(@Half2,'[c'+cast(@Digits as varchar(50))+']',@FlowNum),    UseTime = GETDATE() where BizSeqType=@BizSeqTypeendselect BizSeqValue from SM_BizSeqNo where BizSeqType=@BizSeqTypeEND