sql 单号编码规则

--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                        
*/