获取单据编号的存储过程:
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
