--以下代码生成的编号长度为12,前6位为日期信息,格式为YYMMDD,后6位为流水号。 --创建得到当前日期的视图 CREATE VIEW v_GetDate AS SELECT dt=CONVERT(CHAR(6),GETDATE(),12) GO--得到新编号的函数 CREATE FUNCTION f_NextBH() RETURNS char(12) AS BEGIN DECLARE @dt CHAR(6) SELECT @dt=dt FROM v_GetDate RETURN( SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK) WHERE BH like @dt+'%') END GO--在表中应用函数 CREATE TABLE tb( BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(), col int)--插入资料 INSERT tb(col) VALUES(1) INSERT tb(col) VALUES(2) INSERT tb(col) VALUES(3) DELETE tb WHERE col=3 INSERT tb(col) VALUES(4) INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)--显示结果 SELECT * FROM tb /*--结果 BH col ------------------- ----------- 050405000001 1 050405000002 2 050405000003 4 050405000004 14 --*/
--编号处理(独立表法)生成格式 JHA090526000001 --构建编号表如maxbh if OBJECT_ID('maxbh') is not null drop table maxbh --判断编号表是否存在 create table maxbh( name nvarchar(20), --名称 head nvarchar(20), --标识 dd int, --天 lastyearmonth nvarchar(7), --上次生成编号的年月 currentbh int, --当前编号 bhintlen int, --编号数字部分长度 decription nvarchar(100)) --描述
--插入测试数据 insert maxbh(name,head,dd,lastyearmonth,currentbh,bhintlen,decription) select '采购验收入库单','JHA',21,'2009-04',1,6,'采购验收入库单' union all select '采购验收入库单','JHA',22,'2009-04',1,6,'采购验收入库单' union all select '采购验收入库单','JHA',23,'2009-04',1,6,'采购验收入库单' union all select '采购验收入库单','JHA',24,'2009-04',1,6,'采购验收入库单' union all select '采购验收入库单','JHA',25,'2009-04',1,6,'采购验收入库单' union all select '采购验收入库单','JHA',26,'2009-04',1,6,'采购验收入库单' union all select '采购验收入库单','JHA',27,'2009-04',1,6,'采购验收入库单' union all select '采购验收入库单','JHA',28,'2009-04',1,6,'采购验收入库单' union all select '采购验收入库单','JHA',29,'2009-04',1,6,'采购验收入库单' union all select '采购验收入库单','JHA',30,'2009-04',1,6,'采购验收入库单' union all select '销售出库单','XSA',22,'2009-05',1,6,'销售出库单' union all select '销售出库单','XSA',23,'2009-05',1,6,'销售出库单' union all select '销售出库单','XSA',24,'2009-05',1,6,'销售出库单' union all select '销售出库单','XSA',25,'2009-05',1,6,'销售出库单' union all select '销售出库单','XSA',26,'2009-04',1,6,'销售出库单' union all select '销售出库单','XSA',27,'2009-05',1,6,'销售出库单' union all select '销售出库单','XSA',28,'2009-05',1,6,'销售出库单' union all select '销售出库单','XSA',29,'2009-05',1,6,'销售出库单' union all select '销售出库单','XSA',30,'2009-05',1,6,'销售出库单'declare @head nvarchar(3), @rq datetime, --如果日期为NULL,则为当前日期 @djbh nvarchar(30) set @head='XSA' if @rq is null set @rq=convert(varchar(10),GETDATE(),21) --如果日期为空,则为当前日期 if ISDATE(@rq)=0 set @rq=GETDATE() --如果日期非法,则为当前日期 update maxbh set @djbh=head+convert(nvarchar(10),@rq,12)+right(power(10,bhintlen)+ case when lastyearmonth<CONVERT(nvarchar(7),@rq,21) then 1 else currentbh+1 end,bhintlen), currentbh=case when lastyearmonth<CONVERT(nvarchar(7),@rq,21) then 1 else currentbh+1 end, lastyearmonth=convert(nvarchar(7),@rq,21) where head=@head and lastyearmonth<=CONVERT(nvarchar(7),@rq,21) and dd=DAY(@rq) print @djbh go真是巧
create table tb ( id int identity(1,1), 单据编号 as convert(varchar(8),getdate(),112)+right('000'+ltrim(id),4), name varchar(30) )insert tb select 'csdn'select * from tbdrop table tb/** id 单据编号 name ----------- ---------------- ------------------------------ 1 200905260001 csdn(所影响的行数为 1 行) **/
根据日期自动生成编码系列号的实例 --以下代码生成的编号长度为12,前6位为日期信息,格式为YYMMDD,后6位为流水号。 --创建得到当前日期的视图 CREATE VIEW v_GetDate AS SELECT dt=CONVERT(CHAR(6),GETDATE(),12) GO--得到新编号的函数 CREATE FUNCTION f_NextBH() RETURNS char(12) AS BEGIN DECLARE @dt CHAR(6) SELECT @dt=dt FROM v_GetDate RETURN( SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK) WHERE BH like @dt+'%') END GO--在表中应用函数 CREATE TABLE tb( BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(), col int)--插入资料 INSERT tb(col) VALUES(1) INSERT tb(col) VALUES(2) INSERT tb(col) VALUES(3) DELETE tb WHERE col=3 INSERT tb(col) VALUES(4) INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)--显示结果 SELECT * FROM tb /*--结果 BH col ------------------- ----------- 050405000001 1 050405000002 2 050405000003 4 050405000004 14 --*/ -------------------------------------------------根据输入类别自动生成编号示例--表 create table tab(a varchar(20),b varchar(100)) go--触发器 create trigger tr_insert on tab instead of insert as declare @dt varchar(10) set @dt='-'+convert(varchar(10),getdate(),120)select * into #t from inserted a join( select gid_new=b.a,sid_new=1000001+isnull(max(cast(right(a.a,5) as int)),0) from tab a right join inserted b on charindex(b.a+@dt,a.a)=1 group by b.a )b on a.a=b.gid_new order by b.gid_newdeclare @nid varchar(100),@a int update #t set @a=case @nid when gid_new then @a+1 else sid_new end ,a=gid_new+@dt+'-'+right(@a,5) ,@nid=gid_newinsert tab select a,b from #t go--插入数据 insert tab values('sj','2222') insert tab select 'sj','324324' union all select 'sj','33343' union all select 'dj','33343' union all select 'dj','24324' union all select 'sj','24234'--显示插入结果 select * from tab order by a go--删除测试 drop table tab/*--测试结果a b -------------------- -------- dj-2004-07-1-00001 33343 dj-2004-07-1-00002 24324 sj-2004-07-1-00001 2222 sj-2004-07-1-00002 324324 sj-2004-07-1-00003 33343 sj-2004-07-1-00004 24234(所影响的行数为 6 行) --*/
--假设单据编号是这样开始的:200905260001,后面4位为流水号。 --不存在今天编号的话,则流水号为0001,存在的话,在原来流水号基础上+1 select convert(varchar(8),getdate(),112)+right('0000'+ltrim(cast(isnull((select max(right(单据编号,4)) from tb where left(单据编号,8)=convert(varchar(8),getdate(),112)),0) as int)),4)
根据日期自动生成编码系列号的实例 --以下代码生成的编号长度为12,前6位为日期信息,格式为YYMMDD,后6位为流水号。 --创建得到当前日期的视图 CREATE VIEW v_GetDate AS SELECT dt=CONVERT(CHAR(6),GETDATE(),12) GO--得到新编号的函数 CREATE FUNCTION f_NextBH() RETURNS char(12) AS BEGIN DECLARE @dt CHAR(6) SELECT @dt=dt FROM v_GetDate RETURN( SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK) WHERE BH like @dt+'%') END GO--在表中应用函数 CREATE TABLE tb( BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(), col int)--插入资料 INSERT tb(col) VALUES(1) INSERT tb(col) VALUES(2) INSERT tb(col) VALUES(3) DELETE tb WHERE col=3 INSERT tb(col) VALUES(4) INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)--显示结果 SELECT * FROM tb /*--结果 BH col ------------------- ----------- 050405000001 1 050405000002 2 050405000003 4 050405000004 14 --*/ -------------------------------------------------根据输入类别自动生成编号示例--表 create table tab(a varchar(20),b varchar(100)) go--触发器 create trigger tr_insert on tab instead of insert as declare @dt varchar(10) set @dt='-'+convert(varchar(10),getdate(),120)select * into #t from inserted a join( select gid_new=b.a,sid_new=1000001+isnull(max(cast(right(a.a,5) as int)),0) from tab a right join inserted b on charindex(b.a+@dt,a.a)=1 group by b.a )b on a.a=b.gid_new order by b.gid_newdeclare @nid varchar(100),@a int update #t set @a=case @nid when gid_new then @a+1 else sid_new end ,a=gid_new+@dt+'-'+right(@a,5) ,@nid=gid_newinsert tab select a,b from #t go--插入数据 insert tab values('sj','2222') insert tab select 'sj','324324' union all select 'sj','33343' union all select 'dj','33343' union all select 'dj','24324' union all select 'sj','24234'--显示插入结果 select * from tab order by a go--删除测试 drop table tab/*--测试结果a b -------------------- -------- dj-2004-07-1-00001 33343 dj-2004-07-1-00002 24324 sj-2004-07-1-00001 2222 sj-2004-07-1-00002 324324 sj-2004-07-1-00003 33343 sj-2004-07-1-00004 24234(所影响的行数为 6 行) --*/
--以下代码生成的编号长度为12,前6位为日期信息,格式为YYMMDD,后6位为流水号。 --创建得到当前日期的视图 CREATE VIEW v_GetDate AS SELECT dt=CONVERT(CHAR(6),GETDATE(),12) GO--得到新编号的函数 CREATE FUNCTION f_NextBH() RETURNS char(12) AS BEGIN DECLARE @dt CHAR(6) SELECT @dt=dt FROM v_GetDate RETURN( SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK) WHERE BH like @dt+'%') END GO--在表中应用函数 CREATE TABLE tb( BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(), col int)--插入资料 INSERT tb(col) VALUES(1) INSERT tb(col) VALUES(2) INSERT tb(col) VALUES(3) DELETE tb WHERE col=3 INSERT tb(col) VALUES(4) INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
--编号表 CREATE TABLE tb_NO( Name char(2) NOT NULL, --编号种类的名称 Days int NOT NULL, --保存的是该种编号那一天的当前编号 Head nvarchar(10) NOT NULL DEFAULT '', --编号的前缀 CurrentNo int NOT NULL DEFAULT 0, --当前编号 BHLen int NOT NULL DEFAULT 6, --编号数字部分长度 YearMoth int NOT NULL --上次生成编号的年月,格式YYYYMM DEFAULT CONVERT(CHAR(6),GETDATE(),112), DESCRIPTION NVARCHAR(50), --编号种类说明 TableName sysname NOT NULL, --当前编号对应的原始表名 KeyFieldName sysname NOT NULL, --当前编号对应的原始表编号字段名 PRIMARY KEY(Name,Days))--这里以一种单据的7天的资料来做测试 INSERT tb_NO SELECT 'CG',1,'CG',0,4,200501,N'采购订单',N'tb',N'bh' UNION ALL SELECT 'CG',2,'CG',0,4,200501,N'采购订单',N'tb',N'bh' UNION ALL SELECT 'CG',3,'CG',0,4,200501,N'采购订单',N'tb',N'bh' UNION ALL SELECT 'CG',4,'CG',0,4,200501,N'采购订单',N'tb',N'bh' UNION ALL SELECT 'CG',5,'CG',0,4,200501,N'采购订单',N'tb',N'bh' UNION ALL SELECT 'CG',6,'CG',0,4,200501,N'采购订单',N'tb',N'bh' UNION ALL SELECT 'CG',7,'CG',0,4,200501,N'采购订单',N'tb',N'bh' GO--获取新编号的存储过程 CREATE PROC p_NextBH @Name char(2), --编号种类 @Date datetime=NULL, --要获取的当前日期,不指定则为系统当前日期 @BH nvarchar(20) OUTPUT --新编号 AS IF @Date IS NULL SET @Date=GETDATE() BEGIN TRAN --从编号表中获取新编号 UPDATE tb_NO SET @BH=Head +CONVERT(CHAR(6),@Date,12) +RIGHT(POWER(10,BHLen) +CASE WHEN YearMoth=CONVERT(char(6),@Date,112) THEN CurrentNo+1 ELSE 1 END ,BHLen), CurrentNo=CASE WHEN YearMoth=CONVERT(char(6),@Date,112) THEN CurrentNo+1 ELSE 1 END, YearMoth=CONVERT(char(6),@Date,112) WHERE Name=@Name AND Days=DAY(@Date) AND YearMoth<=CONVERT(char(6),@Date,112) --如果要获取的编号在编号表中已经过期,则直接从原始表中取编号 IF @@ROWCOUNT=0 BEGIN DECLARE @s nvarchar(4000) SELECT @s=N'SELECT @BH=' +QUOTENAME(Head+CONVERT(CHAR(6),@Date,12),N'''') +N'+RIGHT('+CAST(POWER(10,BHLen)+1 as varchar) +N'+ISNULL(RIGHT(MAX('+QUOTENAME(KeyFieldName) +N'),'+CAST(BHLen as varchar) +N'),0),'+CAST(BHLen as varchar) +N') FROM '+QUOTENAME(TableName) +N' WITH(XLOCK,PAGLOCK) WHERE ' +QUOTENAME(KeyFieldName) +N' like '+QUOTENAME(Head+CONVERT(CHAR(6),@Date,12)+N'%',N'''') FROM tb_NO WHERE Name=@Name AND Days=DAY(@Date) AND YearMoth>CONVERT(char(6),@Date,112) IF @@ROWCOUNT>0 EXEC sp_executesql @s,N'@BH nvarchar(20) OUTPUT',@BH OUTPUT END COMMIT TRAN GOCREATE TABLE tb(BH char(12)) --获取 CG 的新编号 DECLARE @bh char(12) EXEC p_NextBH 'CG','2005-1-1',@bh OUT SELECT @bh --结果: CG0501010001EXEC p_NextBH 'CG','2005-1-1',@bh OUT SELECT @bh --结果: CG0501010002EXEC p_NextBH 'CG','2005-1-2',@bh OUT SELECT @bh --结果: CG0501020001EXEC p_NextBH 'CG','2005-2-2',@bh OUT SELECT @bh --结果: CG0402020001EXEC p_NextBH 'CG','2004-2-2',@bh OUT SELECT @bh --结果: CG0402020001 GO
--创建得到当前日期的视图
CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(CHAR(6),GETDATE(),12)
GO--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
DECLARE @dt CHAR(6)
SELECT @dt=dt FROM v_GetDate
RETURN(
SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6)
FROM tb WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%')
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)--显示结果
SELECT * FROM tb
/*--结果
BH col
------------------- -----------
050405000001 1
050405000002 2
050405000003 4
050405000004 14
--*/
--构建编号表如maxbh
if OBJECT_ID('maxbh') is not null drop table maxbh --判断编号表是否存在
create table maxbh(
name nvarchar(20), --名称
head nvarchar(20), --标识
dd int, --天
lastyearmonth nvarchar(7), --上次生成编号的年月
currentbh int, --当前编号
bhintlen int, --编号数字部分长度
decription nvarchar(100)) --描述
--插入测试数据
insert maxbh(name,head,dd,lastyearmonth,currentbh,bhintlen,decription)
select '采购验收入库单','JHA',21,'2009-04',1,6,'采购验收入库单'
union all select '采购验收入库单','JHA',22,'2009-04',1,6,'采购验收入库单'
union all select '采购验收入库单','JHA',23,'2009-04',1,6,'采购验收入库单'
union all select '采购验收入库单','JHA',24,'2009-04',1,6,'采购验收入库单'
union all select '采购验收入库单','JHA',25,'2009-04',1,6,'采购验收入库单'
union all select '采购验收入库单','JHA',26,'2009-04',1,6,'采购验收入库单'
union all select '采购验收入库单','JHA',27,'2009-04',1,6,'采购验收入库单'
union all select '采购验收入库单','JHA',28,'2009-04',1,6,'采购验收入库单'
union all select '采购验收入库单','JHA',29,'2009-04',1,6,'采购验收入库单'
union all select '采购验收入库单','JHA',30,'2009-04',1,6,'采购验收入库单'
union all select '销售出库单','XSA',22,'2009-05',1,6,'销售出库单'
union all select '销售出库单','XSA',23,'2009-05',1,6,'销售出库单'
union all select '销售出库单','XSA',24,'2009-05',1,6,'销售出库单'
union all select '销售出库单','XSA',25,'2009-05',1,6,'销售出库单'
union all select '销售出库单','XSA',26,'2009-04',1,6,'销售出库单'
union all select '销售出库单','XSA',27,'2009-05',1,6,'销售出库单'
union all select '销售出库单','XSA',28,'2009-05',1,6,'销售出库单'
union all select '销售出库单','XSA',29,'2009-05',1,6,'销售出库单'
union all select '销售出库单','XSA',30,'2009-05',1,6,'销售出库单'declare @head nvarchar(3),
@rq datetime, --如果日期为NULL,则为当前日期
@djbh nvarchar(30)
set @head='XSA'
if @rq is null set @rq=convert(varchar(10),GETDATE(),21) --如果日期为空,则为当前日期
if ISDATE(@rq)=0 set @rq=GETDATE() --如果日期非法,则为当前日期
update maxbh set @djbh=head+convert(nvarchar(10),@rq,12)+right(power(10,bhintlen)+
case
when lastyearmonth<CONVERT(nvarchar(7),@rq,21)
then 1
else currentbh+1 end,bhintlen),
currentbh=case when lastyearmonth<CONVERT(nvarchar(7),@rq,21) then 1 else currentbh+1 end,
lastyearmonth=convert(nvarchar(7),@rq,21)
where head=@head
and lastyearmonth<=CONVERT(nvarchar(7),@rq,21)
and dd=DAY(@rq)
print @djbh
go真是巧
(
id int identity(1,1),
单据编号 as convert(varchar(8),getdate(),112)+right('000'+ltrim(id),4),
name varchar(30)
)insert tb select 'csdn'select * from tbdrop table tb/**
id 单据编号 name
----------- ---------------- ------------------------------
1 200905260001 csdn(所影响的行数为 1 行)
**/
根据日期自动生成编码系列号的实例
--以下代码生成的编号长度为12,前6位为日期信息,格式为YYMMDD,后6位为流水号。
--创建得到当前日期的视图
CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(CHAR(6),GETDATE(),12)
GO--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
DECLARE @dt CHAR(6)
SELECT @dt=dt FROM v_GetDate
RETURN(
SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6)
FROM tb WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%')
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)--显示结果
SELECT * FROM tb
/*--结果
BH col
------------------- -----------
050405000001 1
050405000002 2
050405000003 4
050405000004 14
--*/ -------------------------------------------------根据输入类别自动生成编号示例--表
create table tab(a varchar(20),b varchar(100))
go--触发器
create trigger tr_insert on tab
instead of insert
as
declare @dt varchar(10)
set @dt='-'+convert(varchar(10),getdate(),120)select * into #t
from inserted a join(
select gid_new=b.a,sid_new=1000001+isnull(max(cast(right(a.a,5) as int)),0)
from tab a
right join inserted b on charindex(b.a+@dt,a.a)=1
group by b.a
)b on a.a=b.gid_new
order by b.gid_newdeclare @nid varchar(100),@a int
update #t set @a=case @nid when gid_new then @a+1 else sid_new end
,a=gid_new+@dt+'-'+right(@a,5)
,@nid=gid_newinsert tab select a,b from #t
go--插入数据
insert tab values('sj','2222')
insert tab select 'sj','324324'
union all select 'sj','33343'
union all select 'dj','33343'
union all select 'dj','24324'
union all select 'sj','24234'--显示插入结果
select * from tab order by a
go--删除测试
drop table tab/*--测试结果a b
-------------------- --------
dj-2004-07-1-00001 33343
dj-2004-07-1-00002 24324
sj-2004-07-1-00001 2222
sj-2004-07-1-00002 324324
sj-2004-07-1-00003 33343
sj-2004-07-1-00004 24234(所影响的行数为 6 行)
--*/
--假设单据编号是这样开始的:200905260001,后面4位为流水号。
--不存在今天编号的话,则流水号为0001,存在的话,在原来流水号基础上+1
select convert(varchar(8),getdate(),112)+right('0000'+ltrim(cast(isnull((select max(right(单据编号,4)) from tb
where left(单据编号,8)=convert(varchar(8),getdate(),112)),0) as int)),4)
根据日期自动生成编码系列号的实例
--以下代码生成的编号长度为12,前6位为日期信息,格式为YYMMDD,后6位为流水号。
--创建得到当前日期的视图
CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(CHAR(6),GETDATE(),12)
GO--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
DECLARE @dt CHAR(6)
SELECT @dt=dt FROM v_GetDate
RETURN(
SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6)
FROM tb WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%')
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)--显示结果
SELECT * FROM tb
/*--结果
BH col
------------------- -----------
050405000001 1
050405000002 2
050405000003 4
050405000004 14
--*/ -------------------------------------------------根据输入类别自动生成编号示例--表
create table tab(a varchar(20),b varchar(100))
go--触发器
create trigger tr_insert on tab
instead of insert
as
declare @dt varchar(10)
set @dt='-'+convert(varchar(10),getdate(),120)select * into #t
from inserted a join(
select gid_new=b.a,sid_new=1000001+isnull(max(cast(right(a.a,5) as int)),0)
from tab a
right join inserted b on charindex(b.a+@dt,a.a)=1
group by b.a
)b on a.a=b.gid_new
order by b.gid_newdeclare @nid varchar(100),@a int
update #t set @a=case @nid when gid_new then @a+1 else sid_new end
,a=gid_new+@dt+'-'+right(@a,5)
,@nid=gid_newinsert tab select a,b from #t
go--插入数据
insert tab values('sj','2222')
insert tab select 'sj','324324'
union all select 'sj','33343'
union all select 'dj','33343'
union all select 'dj','24324'
union all select 'sj','24234'--显示插入结果
select * from tab order by a
go--删除测试
drop table tab/*--测试结果a b
-------------------- --------
dj-2004-07-1-00001 33343
dj-2004-07-1-00002 24324
sj-2004-07-1-00001 2222
sj-2004-07-1-00002 324324
sj-2004-07-1-00003 33343
sj-2004-07-1-00004 24234(所影响的行数为 6 行)
--*/
--以下代码生成的编号长度为12,前6位为日期信息,格式为YYMMDD,后6位为流水号。
--创建得到当前日期的视图
CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(CHAR(6),GETDATE(),12)
GO--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
DECLARE @dt CHAR(6)
SELECT @dt=dt FROM v_GetDate
RETURN(
SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6)
FROM tb WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%')
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
--编号表
CREATE TABLE tb_NO(
Name char(2) NOT NULL, --编号种类的名称
Days int NOT NULL, --保存的是该种编号那一天的当前编号
Head nvarchar(10) NOT NULL DEFAULT '', --编号的前缀
CurrentNo int NOT NULL DEFAULT 0, --当前编号
BHLen int NOT NULL DEFAULT 6, --编号数字部分长度
YearMoth int NOT NULL --上次生成编号的年月,格式YYYYMM
DEFAULT CONVERT(CHAR(6),GETDATE(),112),
DESCRIPTION NVARCHAR(50), --编号种类说明
TableName sysname NOT NULL, --当前编号对应的原始表名
KeyFieldName sysname NOT NULL, --当前编号对应的原始表编号字段名
PRIMARY KEY(Name,Days))--这里以一种单据的7天的资料来做测试
INSERT tb_NO SELECT 'CG',1,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',2,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',3,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',4,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',5,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',6,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
UNION ALL SELECT 'CG',7,'CG',0,4,200501,N'采购订单',N'tb',N'bh'
GO--获取新编号的存储过程
CREATE PROC p_NextBH
@Name char(2), --编号种类
@Date datetime=NULL, --要获取的当前日期,不指定则为系统当前日期
@BH nvarchar(20) OUTPUT --新编号
AS
IF @Date IS NULL SET @Date=GETDATE()
BEGIN TRAN
--从编号表中获取新编号
UPDATE tb_NO SET
@BH=Head
+CONVERT(CHAR(6),@Date,12)
+RIGHT(POWER(10,BHLen)
+CASE
WHEN YearMoth=CONVERT(char(6),@Date,112)
THEN CurrentNo+1
ELSE 1 END
,BHLen),
CurrentNo=CASE
WHEN YearMoth=CONVERT(char(6),@Date,112)
THEN CurrentNo+1
ELSE 1 END,
YearMoth=CONVERT(char(6),@Date,112)
WHERE Name=@Name
AND Days=DAY(@Date)
AND YearMoth<=CONVERT(char(6),@Date,112) --如果要获取的编号在编号表中已经过期,则直接从原始表中取编号
IF @@ROWCOUNT=0
BEGIN
DECLARE @s nvarchar(4000)
SELECT @s=N'SELECT @BH='
+QUOTENAME(Head+CONVERT(CHAR(6),@Date,12),N'''')
+N'+RIGHT('+CAST(POWER(10,BHLen)+1 as varchar)
+N'+ISNULL(RIGHT(MAX('+QUOTENAME(KeyFieldName)
+N'),'+CAST(BHLen as varchar)
+N'),0),'+CAST(BHLen as varchar)
+N') FROM '+QUOTENAME(TableName)
+N' WITH(XLOCK,PAGLOCK) WHERE '
+QUOTENAME(KeyFieldName)
+N' like '+QUOTENAME(Head+CONVERT(CHAR(6),@Date,12)+N'%',N'''')
FROM tb_NO
WHERE Name=@Name
AND Days=DAY(@Date)
AND YearMoth>CONVERT(char(6),@Date,112)
IF @@ROWCOUNT>0
EXEC sp_executesql @s,N'@BH nvarchar(20) OUTPUT',@BH OUTPUT
END
COMMIT TRAN
GOCREATE TABLE tb(BH char(12))
--获取 CG 的新编号
DECLARE @bh char(12)
EXEC p_NextBH 'CG','2005-1-1',@bh OUT
SELECT @bh
--结果: CG0501010001EXEC p_NextBH 'CG','2005-1-1',@bh OUT
SELECT @bh
--结果: CG0501010002EXEC p_NextBH 'CG','2005-1-2',@bh OUT
SELECT @bh
--结果: CG0501020001EXEC p_NextBH 'CG','2005-2-2',@bh OUT
SELECT @bh
--结果: CG0402020001EXEC p_NextBH 'CG','2004-2-2',@bh OUT
SELECT @bh
--结果: CG0402020001
GO