--SET QUOTED_IDENTIFIER ON|OFF --SET ANSI_NULLS ON|OFF --GO /* Description:单号编码规则 @key:开头字符串 @datelen:日期长度 2:年的后两位 4:年 6:年月 8:年月日 @numlen:流水号长度 例如0:1 4:0001 @spacer: 默认空 */ ALTER PROC Pro_realtime_GetMaxBillNo ( @key NVARCHAR(10) = '' , @datelen INT = 0 , @numlen INT = 0 , @spacer NVARCHAR(1) = '' ) AS BEGIN SET IMPLICIT_TRANSACTIONS OFF BEGIN TRAN IF ( @key IS NULL ) BEGIN SET @key = ''; END DECLARE @datestr NVARCHAR(8) --日期间隔 IF ( @datelen <= 0 ) BEGIN SET @datestr = ''; END ELSE IF ( @datelen = 2 ) BEGIN SET @datestr = RIGHT(YEAR(GETDATE()), 2); END ELSE BEGIN SET @datestr = LEFT(CONVERT(VARCHAR(8), GETDATE(), 112), @datelen) END IF ( @spacer IS NULL ) BEGIN SET @spacer = ''; END IF NOT EXISTS ( SELECT 1 FROM dbo.t_realtime_BillNo WITH ( TABLOCKX ) WHERE FKey = @key AND FDateLen = @datelen AND FNumLen = @numlen AND FDateStr = @datestr AND FSpacer = @spacer ) BEGIN INSERT dbo.t_realtime_BillNo ( FKey, FKeyLen, FDateStr, FDateLen, FNum, FNumLen, FBillNo, FCTime, FSpacer ) VALUES ( @key, LEN(@key), @datestr, @datelen, 1, @numlen, CONCAT(@key, CASE WHEN @key = '' THEN '' ELSE @spacer END, @datestr, CASE WHEN @datestr = '' THEN '' ELSE @spacer END, CASE WHEN @numlen <= 0 THEN '1' ELSE STUFF(CONVERT(NVARCHAR(20), POWER(10, @numlen) + 1), 1, 1, '') END), GETDATE(), @spacer ) END ELSE BEGIN UPDATE dbo.t_realtime_BillNo WITH ( TABLOCKX ) SET FNum+= 1, FBillNo = CONCAT(FKey, CASE WHEN FKey = '' THEN '' ELSE FSpacer END, FDateStr, CASE WHEN FDateStr = '' THEN '' ELSE FSpacer END, CASE WHEN FNumLen <= 0 THEN CAST(FNum + 1 AS NVARCHAR(11)) ELSE STUFF(CONVERT(NVARCHAR(20), POWER(10, FNumLen) + FNum + 1), 1, 1, '') END) WHERE FKey = @key AND FDateLen = @datelen AND FNumLen = @numlen AND FDateStr = @datestr AND FSpacer = @spacer END COMMIT TRAN; SELECT FBillNo FROM dbo.t_realtime_BillNo WITH ( TABLOCKX ) WHERE FKey = @key AND FDateLen = @datelen AND FNumLen = @numlen AND FDateStr = @datestr AND FSpacer = @spacer END GO --SET QUOTED_IDENTIFIER ON|OFF --SET ANSI_NULLS ON|OFF --GO -- drop table t_realtime_BillNo /* CREATE TABLE t_realtime_BillNo ( FKey NVARCHAR(10) , FKeyLen INT , FDateStr NVARCHAR(8) , FDateLen INT , FNum INT , FNumLen INT , FSpacer nvarchar(1) NOT NULL DEFAULT(''), FBillNo NVARCHAR(30) , FCTime DATETIME ) */ -- TRUNCATE TABLE dbo.t_realtime_BillNo /* EXEC dbo.Pro_realtime_GetMaxBillNo @key = N'A01A', -- nvarchar(10) @datelen = 1, @numlen = 2, @spacer=' ' SELECT * from dbo.t_realtime_BillNo */
标签:编码,BEGIN,SET,OFF,--,sql,datelen,key,规则 From: https://www.cnblogs.com/miaololi/p/17282638.html