存储过程生成流水号

格式如A20110915001，第1位写死A，2-5位为年，6-7位为月，8-9位为日，最后三位为流水号.

/****** Object:  StoredProcedure [dbo].[usp_SaleRetrun_CreateAutoApplyBatchId]    Script Date: 02/07/2012 09:55:40 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER OFF
GO

CREATE  PROCEDURE [dbo].[usp_CreateAutoBatchId]


AS
BEGIN
SET NOCOUNT ON;
declare @oldValue varchar(12)
declare @newValue varchar(12)
declare @newCode varchar(3)
declare @oldCode varchar(3)
declare @oldYearMonthDay varchar(9)
declare @newYearMonthDay varchar(9)

set @newYearMonthDay = CONVERT(varchar(8), GETDATE(), 112)
set @newYearMonthDay ='R'+@newYearMonthDay
select @oldValue = pValue from Parameter with (rowlock) where pName='Program_Batch_Id' and pActive='Y'
set @oldYearMonthDay = substring(@oldValue,1,9)
set @oldCode = substring(@oldValue,10,3)


if @newYearMonthDay = @oldYearMonthDay --如果新的年月日编号与旧的相同
begin
-- 旧序号＋1
set @newCode = cast(cast(@oldCode as int)+1 as varchar(3))
while len(@newCode)<3
begin
set @newCode = '0' + @newCode
end
set @newValue = @oldYearMonthDay + @newCode
end
else --采用新的年月编号，序号从"001"开始
begin
set @newCode = '001'
set @newValue = @newYearMonthDay + @newCode
end

    Begin transaction           --开始事务
update Parameter with (rowlock) set pValue = @newValue  where pName='Program_Batch_Id' and pActive='Y'
if @@ERROR > 0
ROLLBACK
Else
COMMIT

END

GO