使用編号表按日期生成流水号的示例.sql

--編号表

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

CREATE TABLE tb(BH char(12))

--擷取 CG 的新編号

DECLARE @bh char(12)

EXEC p_NextBH 'CG','2005-1-1',@bh OUT

SELECT @bh

--結果: CG0501010001

--結果: CG0501010002

EXEC p_NextBH 'CG','2005-1-2',@bh OUT

--結果: CG0501020001

EXEC p_NextBH 'CG','2005-2-2',@bh OUT

--結果: CG0402020001

EXEC p_NextBH 'CG','2004-2-2',@bh OUT

chaunceyhao