业务单号自动增长的处理办法 eg: R000000001

最新推荐文章于 2021-02-25 11:55:15 发布

even0220

最新推荐文章于 2021-02-25 11:55:15 发布

阅读量1.1k

点赞数

分类专栏： Sql Server

本文链接：https://blog.csdn.net/even0220/article/details/8483499

版权

Sql Server 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

/* 1:创建测试用的表结构 */
create table tablename(pono char(10),b int)
go
/* 2:用自定义函数来得到单号 */
create function getDH() 
returns char(10) 
As 
begin 
    declare @dh1 char(10),@dh2 char(10) 
    select @dh1 = max(pono) from  tableName 
    Set @dh1 = IsNull(@dh1,'R000000000') 
    select @dh2 = Left(@dh1,1) + right(convert(varchar(8),today,112),6) + '001' from vGetdate 
    set @dh2 = left(@dh1,1) + '000000001'
    if @dh1 >= @dh2 
    begin 
        set @dh2 = left(@dh1,1) + right('000000000'+ cast(cast(right(@dh1,9) as int)+1 as varchar),9) 
    end    
    return(@dh2) 
end 
go 
/* 3:在字段默认值中填入 dbo.getdh() */
alter table tablename add constraint df_tablename_1 default(dbo.getdh()) for pono
/* 4:测试：*/
insert tablename(b) values(1)
insert tablename(b) values(2)
Select * from tablename