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 |
