22,209
社区成员
发帖
与我相关
我的任务
分享
--日期配合自增列
DECLARE @T TABLE(ID INT IDENTITY(1,1),订单编号 AS CONVERT(VARCHAR(10),GETDATE(),112)+RIGHT('0000'+LTRIM(ID),4),NAME VARCHAR(10))
INSERT @T SELECT 'A'
INSERT @T SELECT 'B'
INSERT @T SELECT 'C'
SELECT * FROM @T
/*ID 订单编号 NAME
----------- ------------------ ----------
1 200905120001 A
2 200905120002 B
3 200905120003 C
*/
--编号表
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
--以下代码生成的编号长度为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 FUNCTION [dbo].[F_OAGetNewCRNo] (@InDate smalldatetime)
RETURNS varchar(20)
AS
BEGIN
Declare @Rst varchar(20),@MaxNo int ,@CurrentDate varchar(20)
Select @Rst = '',@MaxNo = 0
--取得当前日期字符串
Select @CurrentDate = Cast(year(@InDate) as varchar) + --年
right('00' + cast(month(@InDate) as varchar),2) + -- 月
right('00' + cast(day(@Indate) as varchar),2)
Select @MaxNo = max(cast(right(fCRNo,4) as int)) From TOA_CarRepair
Where year(@InDate) = cast(left(fCRNo,4) as int) and
month(@InDate) = Cast(substring(fCRNo,5,2) as int) and
day(@InDate) = cast(substring(fCRNo,7,2) as int)
if (isnull(@MaxNo,0) >0)
Begin
select @Rst = @CurrentDate + right('000' + cast(@MaxNo + 1 as varchar),4)
End
else
Select @Rst = @CurrentDate + '0001'
RETURN @Rst
END
GO