生成唯一编号(序列号)--sql存储过程

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('0',@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 '1001',@strNumberKey  out