CREATE procedure [dbo].[P_Sys_GetSerialNo] --取业务序列号

  @SeqType   int, --序列号类别,4位数,如:10+2+1 即1021

  @GetCount   int=1 ,   --要取的编号数

  @IDList   varchar(8000) out, --返回的序号列表

  @ErrNum   int=0   out, --执行错误号

  @ErrMsg   varchar(200)   out --错误信息

  as

declare

@tableNameA varchar(50),

@FieldNameA   varchar(50),

@liCurrID int,

@liLength int,

@liCount int,

@liCurrLength int,

@lsID varchar(50),

@lsTmp varchar(20)  

set @IDList=''

set @ErrNum=0

if @SeqType<1000 or @SeqType>=10000

begin

  set @ErrNum=1

  set @ErrMsg='参数@SeqType的值不正确,必须为四位数的整数！'

  return

end  

if @GetCount<1 or @GetCount>100

begin

  set @ErrNum=1

  set @ErrMsg='参数@GetCount必须是大于0且小等于100的数字!'

  return

end  

set @tableNameA=Upper('GetSerialNo_'+Ltrim(Str(@SeqType)));

set @FieldNameA=convert(varchar(10),getdate(),112)

set @FieldNameA=RIGHT(@FieldNameA,6)  

--目的：允许重复读，以降低两个连接同时读相同的值，造成流水号重复的可能性。

BEGIN tran

 select @liCurrID=isnull(nCount,0),@liLength=isnull(nLengthd,6)

 from TAB_AutoNumber WITH(updlock)

 where vcTableName=@tableNameA and vcFieldName=@FieldNameA  

 IF @@rowcount=0

 BEGIN

   SET @liCurrID=ISNULL(@liCurrID,0)

   SET @liLength=ISNULL(@liLength,6)  

   insert into TAB_AutoNumber(vcTableName,vcFieldName,vcPrefixs,vcSuffixs,nCount,nLengthd)

    values(@tableNameA,@FieldNameA,'','',1,6)

   select @liCurrID=1,@liLength=6

 END  

 update TAB_AutoNumber set nCount=@liCurrID+@GetCount

 where vcTableName=@tableNameA and vcFieldName=@FieldNameA

COMMIT tran   

if @@error<>0

begin

  set @ErrNum=2

  set @ErrMsg='更新最大编号出错！'

  return

END

/*

begin tran

 if exists(select vcTableName from TAB_AutoNumber with(nolock) where vcTableName=@tableNameA and vcFieldName=@FieldNameA)

 begin

   select @liCurrID=isnull(nCount,0),@liLength=isnull(nLengthd,6)

   from TAB_AutoNumber with(updlock) where vcTableName=@tableNameA and vcFieldName=@FieldNameA

 end else

 begin

   insert into TAB_AutoNumber(vcTableName,vcFieldName,vcPrefixs,vcSuffixs,nCount,nLengthd)

    values(@tableNameA,@FieldNameA,'','',1,6)

   select @liCurrID=1,@liLength=6

 end

 update TAB_AutoNumber set nCount=@liCurrID+@GetCount

 where vcTableName=@tableNameA and vcFieldName=@FieldNameA

 if @@error<>0

 begin

   set @ErrNum=2

   set @ErrMsg='更新最大编号出错！'

   return

 end

commit tran

*/

set @IDList=''

set @liCount=@liCurrID

while @liCount<@liCurrID+@GetCount

begin

  set @lsID=Ltrim(Str(@liCount))

  begin

    set @liCurrLength=Len(@lsID)

    if @liCurrLength<@liLength

      set @lsTmp=Replicate('',@liLength-@liCurrLength)

    else

      set @lsTmp=''

   --set @lsTmp='9'+RIGHT(@lsTmp,LEN(@lsTmp)-1)

    set @IDList=@IDList+','+@FieldNameA+Ltrim(Str(@SeqType))+@lsTmp+@lsID

  end

  set @liCount=@liCount+1

end

set @IDList=Substring(@IDList,2,len(@IDList)-1)  

    ---示范

 --Declare @vcShoppingCartCNO varchar(30)

 --Declare @ErrNum varchar(30)

 --Declare @ErrMsg varchar(30)

 ----- 1001 用户注册

 ----- 1002 订单类

 ----- 1003 财务类

 --exec P_Sys_GetSerialNo 1001,1,@vcShoppingCartCNO OUT,@ErrNum OUT,@ErrMsg OUT

 --print @vcShoppingCartCNO

 --   --

 --   select *  from  tab_AutoNumber

 --   delete tab_AutoNumber  

-----------------------------------------------------------------------------------------

CREATE PROC [dbo].[P_Sys_GetSerialNoBy]

@ntype int,

@SerialNo varchar(20) out

as

BEGIN

Declare @vcShoppingCartCNO varchar(30)

 Declare @ErrNum varchar(30)

 Declare @ErrMsg varchar(30)

 --- 1001 用户类

 --- 1002 企业类

 --- 1003 财务类

 --- 1004 简历

 --- 1005 职位

 --- 1006 其他

 exec P_Sys_GetSerialNo @ntype,1,@vcShoppingCartCNO OUT,@ErrNum OUT,@ErrMsg OUT

 set @SerialNo=@vcShoppingCartCNO  

END  

--Declare @vcShoppingCartCNO varchar(30)

 --Declare @ErrNum varchar(30)

 --Declare @ErrMsg varchar(30)

 ----- 1001 用户注册

 ----- 1002 订单类

 ----- 1003 财务类

 --exec P_Sys_GetSerialNo 1001,1,@vcShoppingCartCNO OUT,@ErrNum OUT,@ErrMsg OUT

 --print @vcShoppingCartCNO  

------------------------------------------------------------------------------------

/**创建表**/

if exists (select * from sysobjects where id = OBJECT_ID('[tab_AutoNumber]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)

DROP TABLE [tab_AutoNumber]

CREATE TABLE [tab_AutoNumber] (

[vcTableName] [varchar]  (50) NOT NULL,

[vcFieldName] [varchar]  (20) NOT NULL,

[vcPrefixs] [varchar]  (20) NULL,

[vcSuffixs] [varchar]  (20) NULL,

[nLengthd] [smallint]  NULL,

[nCount] [int]  NOT NULL,

[vcMemos] [varchar]  (50) NULL)

ALTER TABLE [tab_AutoNumber] WITH NOCHECK ADD  CONSTRAINT [PK_tab_AutoNumber] PRIMARY KEY  NONCLUSTERED ( [vcTableName],[vcFieldName] )

------------------------------------------------------------------------------------

/**执行**/

DECLARE @strNumberKey VARCHAR(20)

EXEC P_Sys_GetSerialNoBy '',@strNumberKey  out