SQL流水号的生成sql语句

create table detail(detailid varchar(50) primary key, proceedingsn varchar(50) not null,typesn int not null,truename varchar(50) not null,number varchar(50) not null,
orgname varchar(50),orgsn varchar(50), phone varchar(50) not null,descr varchar(50),createdate smalldatetime not null,type int not null,reason varchar(500))

create trigger t_insert on detail
INSTEAD OF insert
as
declare @detailid varchar(18),@detailid1 int,@head varchar(12)
select * into #tb from inserted
set @head=convert(varchar,getdate(),112)+replace(convert(varchar(5),getdate(),108),':’,”)
select @detailid=max(detailid) from detail where detailid like @head+’%’
if @detailid is null
set @detailid1=0
else
set @detailid1=cast(substring(@detailid,13,4) as int)
update #tb set @detailid1=@detailid1+1
,detailid=@head+right(‘0000’+cast(@detailid1 as varchar),4)
insert into detail select * from #tb

alter table detail add constraint CK_DETAIL check (typesn=’1′ or typesn=’0′)

alter table detail add constraint CK_DETAIL1 check (type=’1′ or type=’0′ or type=’2′)

--生成流水号

--创建测试表

create table test(id varchar(18),  --流水号,日期(8位)+时间(4位)+流水号(4位)

        name varchar(10)  --其他字段

)

go

--创建生成流水号的触发器

create trigger t_insert on test

INSTEAD OF insert

as

declare @id varchar(18),@id1 int,@head varchar(12)

select * into #tb from inserted

set @head=convert(varchar,getdate(),112)+replace(convert(varchar(5),getdate(),108),':','')

select @id=max(id) from test where id like @head+'%'

if @id is null

        set @id1=0

else

        set @id1=cast(substring(@id,13,4) as int)

update #tb set @id1=@id1+1

        ,id=@head+right('0000'+cast(@id1 as varchar),4)

insert into test select * from #tb

go

--插入数据,进行测试

insert into test(name)

select 'aa'

union all select 'bb'

union all select 'cc'

--修改系统时间,再插入数据测试一次

insert into test(name)

select 'aa'

union all select 'bb'

union all select 'cc'

--显示测试结果

select * from test

--删除测试环境

drop table test

/*--测试结果

id                 name      

------------------ ----------

2004022720430001   aa

2004022720430002   bb

2004022720430003   cc

2004022720430004   aa

2004022720430005   bb

2004022720430006   cc

（所影响的行数为 6 行）

--*/

创建流水号表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) as
begin
if(len(@num)=0)
begin
    return ''
end
declare @curStr varchar(16)
select @curStr = isnull(replicate(@paddingChar,@totalWidth - len(isnull(@num ,0))), '') + @num
return @curStr
end
Create PROCEDURE [dbo].[Biz_GetSeqNo]
    @BizSeqType varchar(50)
AS
BEGIN
    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=@BizSeqType
select @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())
end
else if(charindex('[yy]',@Formula)>0)
begin
    select @Yearindex=charindex('[yy]',@Formula)
    select @year=SUBSTRING(CAST(YEAR(getdate()) as varCHAR(4)),3,2)
end
select @monthindex=charindex('[mm]',@Formula)
if(@monthindex>0)
begin
    select @month=month(getdate())
end
select @dayindex=charindex('[dd]',@Formula)
if(@dayindex>0)
begin
    select @day=day(getdate())
end
select @Half1=@Prefix+@Year+dbo.PadLeft(@Month,'0',2)+dbo.PadLeft(@Day,'0',2)
--select @Half1,@Prefix,@Year
if(@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-6
end
else 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-4
end
else 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-2
end
else
begin
    select @Half2=SUBSTRING(@Formula,LEN(@Prefix)+1,999)
    select @Count=COUNT(1) from SM_BizSeqNo where BizSeqType=@BizSeqType
end
if(@Count>0) --当前流水号+1
begin
    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=@BizSeqType
end
else
begin
    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=@BizSeqType
end
select BizSeqValue from SM_BizSeqNo where BizSeqType=@BizSeqType
END