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