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]GOCREATE 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