获取流水号DEMO

-- 简单例子

create table YW_LSHMax
(FLSHID nvarchar(32) primary key,
FLSHCode nvarchar(20),  --配置编码
FLSHHead nvarchar(10),  --头部
FLSHDATAFomat nvarchar(20), --时间格式
FLSHLenth int,  -- 长度
FLSHMaxInt int)  --最大流水号值
--插入配置独立出来,不要和取流水号混在一起
--初使化数据
insert into YW_LSHMax
(FLSHID,  
 FLSHCode,  FLSHHead,    FLSHDATAFomat, FLSHLenth,  FLSHMaxInt)
values
(REPLACE(newID(),'-',''),
'TEST',   'TEST',     'YYYYMMDD',   5,   0)

insert into YW_LSHMax
(FLSHID,  
 FLSHCode,  FLSHHead,    FLSHDATAFomat, FLSHLenth,  FLSHMaxInt)
values
(REPLACE(newID(),'-',''),
'JH',   'JCWL',     'YYYYMMDD',   5,   0)

/********************************************************************/
CREATE procedure SPGet_LSH
(
 @QLSHCode nvarchar(20) --配置代码
)
as
begin
 SET XACT_ABORT ON
 BEGIN TRY     
 BEGIN TRAN
   update YW_LSHMax set FLSHMaxInt=ISNULL(FLSHMaxInt,0)+1
   where FLSHCode=@QLSHCode
   if @@ROWCOUNT != 1
   BEGIN
   Rollback Tran
   select cast(0 as bit) FbOK, '锁定流水号配置失败,请检查是否有存在配置' FErrMsg,'' FLSHNO
   return
   END
   declare @FLSHMaxInt int,@FLSHHead nvarchar(10),@FLSHDATAFomat nvarchar(20),@FLSHLenth int,@MaxStr nvarchar(10)
   declare @FYear nvarchar(10),@FMonth nvarchar(10),@FDay nvarchar(10)
   declare @FLSHNo nvarchar(50)
   select @FLSHMaxInt=FLSHMaxInt,@FLSHHead=FLSHHead,@FLSHDATAFomat=FLSHDATAFomat,@FLSHLenth=FLSHLenth from YW_LSHMax where FLSHCode=@QLSHCode
   --默认处理，防止组合为null
   set @FLSHHead=ISNULL(@FLSHHead,'') 
   set @FLSHDATAFomat=ISNULL(@FLSHDATAFomat,'YYYYMMDD')
   set @FLSHLenth=ISNULL(@FLSHLenth,4)
   set @MaxStr=@FLSHMaxInt
   while Len(@MaxStr)<@FLSHLenth
   begin
   set @MaxStr='0'+@MaxStr
   end
  
   if @FLSHDATAFomat='YYYYMMDD'
   begin
   set @FYear=DATENAME(yyyy,getdate())
   set @FMonth=DATENAME(MM,getdate())
   set @FDay=DATENAME(DD,getdate())
    set @FLSHDATAFomat= @FYear+@FMonth+@FDay
   end
   else
   begin
    set @FLSHDATAFomat= ''
   end
   set @FLSHNo=@FLSHHead+@FLSHDATAFomat+@MaxStr
   select cast(1 as bit) FbOK, '获取流水号成功' FErrMsg,@FLSHNo FLSHNO

 COMMIT TRAN;    
 END TRY     
 BEGIN CATCH 
  if @@ERROR> 0 --判断 如果两条语句有任何一条出现错误。(如果前面的SQL 语句执行没有错误，则返回0)     
  begin 
   if @@TRANCOUNT>0
   begin
    Rollback Tran --开始执行事务的回滚，恢复转账开始之前的状态
   end  
  end     
 END CATCH 
 SET XACT_ABORT OFF

end

--测试
exec SPGet_LSH 'TEST'
EXEC SPGet_LSH 'JH'