CREATE TABLE [dbo].[BillNo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [BillName] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [MaxNo] [int] NULL,
    [MaxDate] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Prefix] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [NumBit] [int] NULL CONSTRAINT [DF_BillNo_NumBit]  DEFAULT ((0)),
    [Style] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Memo] [text] COLLATE Chinese_PRC_CI_AS NULL,
    [CreateDate] [datetime] NULL CONSTRAINT [DF_BillNo_CreateDate]  DEFAULT (getdate()),
    [EditDate] [datetime] NULL,
    [State] [int] NULL CONSTRAINT [DF_BillNo_State]  DEFAULT ((0)),
 CONSTRAINT [PK_BillNo] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE PROCEDURE [dbo].[p_GetBillNo] --产生按年月日排列的档案号
    @BillName varchar(20),
    @No varchar(20) output, --为产生的档案号
    @NumBit int = 4 --编号位数

AS 
    DECLARE @MaxNo int,
            @MaxDate varchar(20),
            @Prefix varchar(20),
            @xNumBit int,
            @CurrDate varchar(12),
            @FILL_ZERO int,
            @iMaxNo int

    SET @FILL_ZERO = 100000000
    SET @CurrDate = CONVERT(VARCHAR(8), GETDATE(),112)

    SELECT @MaxNo = MaxNo, @MaxDate = MaxDate, @Prefix = Prefix, 

  @xNumBit = NumBit FROM BillNo WITH(XLOCK) WHERE BillName = @BillName 
    IF @@ROWCOUNT <> 1 RETURN -1

    IF @xNumBit <> 0 SET @NumBit = @xNumBit 

    IF @MaxDate <> @CurrDate OR @MaxDate IS NULL OR @MaxNo IS NULL
        SET @iMaxNo = 1
    ELSE
        SET @iMaxNo = @MaxNo + 1

    UPDATE BillNo
         SET MaxNo = @iMaxNo, MaxDate = @CurrDate, EditDate = GetDate() 
      WHERE BillName = @BillName
    IF @@ROWCOUNT <> 1 RETURN -1

    SET @No = COALESCE(@Prefix, '') + @CurrDate + RIGHT(CAST(@FILL_ZERO + @iMaxNo AS
    VARCHAR), @NumBit) 

    RETURN 1