56.使用编号表按日期生成刘少的案例

--编号表
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
GO

CREATE TABLE tb(BH char(12))
--获取 CG 的新编号
DECLARE @bh char(12)
EXEC p_NextBH 'CG','2005-1-1',@bh OUT
SELECT @bh
--结果: CG0501010001

EXEC p_NextBH 'CG','2005-1-1',@bh OUT
SELECT @bh
--结果: CG0501010002

EXEC p_NextBH 'CG','2005-1-2',@bh OUT
SELECT @bh
--结果: CG0501020001

EXEC p_NextBH 'CG','2005-2-2',@bh OUT
SELECT @bh
--结果: CG0402020001

EXEC p_NextBH 'CG','2004-2-2',@bh OUT
SELECT @bh
--结果: CG0402020001
GO