获取单据编号的存储过程

CREATETABLE[dbo].[BillNo](
[Id][int]IDENTITY(1,1)NOTNULL,
[BillName][varchar](20)COLLATEChinese_PRC_CI_ASNOTNULL,
[MaxNo][int]NULL,
[MaxDate][varchar](20)COLLATEChinese_PRC_CI_ASNULL,
[Prefix][varchar](20)COLLATEChinese_PRC_CI_ASNULL,
[NumBit][int]NULLCONSTRAINT[DF_BillNo_NumBit]DEFAULT((0)),
[Style][varchar](50)COLLATEChinese_PRC_CI_ASNULL,
[Memo][text]COLLATEChinese_PRC_CI_ASNULL,
[CreateDate][datetime]NULLCONSTRAINT[DF_BillNo_CreateDate]DEFAULT(getdate()),
[EditDate][datetime]NULL,
[State][int]NULLCONSTRAINT[DF_BillNo_State]DEFAULT((0)),
CONSTRAINT[PK_BillNo]PRIMARYKEYCLUSTERED
(
[Id]ASC
)WITH(IGNORE_DUP_KEY=OFF)ON[PRIMARY]
)ON[PRIMARY]TEXTIMAGE_ON[PRIMARY]

GO

CREATEPROCEDURE[dbo].[p_GetBillNo]--产生按年月日排列的档案号
@BillNamevarchar(20),
@Novarchar(20)output,--为产生的档案号
@NumBitint=4--编号位数

AS
DECLARE@MaxNoint,
@MaxDatevarchar(20),
@Prefixvarchar(20),
@xNumBitint,
@CurrDatevarchar(12),
@FILL_ZEROint,
@iMaxNoint

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

SELECT@MaxNo=MaxNo,@MaxDate=MaxDate,@Prefix=Prefix,@xNumBit=NumBitFROMBillNoWITH(XLOCK)WHEREBillName=@BillName
IF@@ROWCOUNT<>1RETURN-1

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

IF@MaxDate<>@CurrDateOR@MaxDateISNULLOR@MaxNoISNULL
SET@iMaxNo=1
ELSE
SET@iMaxNo=@MaxNo+1

UPDATEBillNo
SETMaxNo=@iMaxNo,MaxDate=@CurrDate,EditDate=GetDate()WHEREBillName=@BillName
IF@@ROWCOUNT<>1RETURN-1

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

RETURN1