根据格式字符串自动生成单据号

本代码为通过特定关键字的字符串自动生成单据号；解决编程中频繁编写代码自动生成单据号。

@SJ ---为格式字符串(以下是关键字含义)

"%Y"表示完整年如:2012
"%y"表示简写年如:12
"%M"表示两位月份如:01
"%m"表示简写月份如:1
"%D"表示天如:08
"%d"表示简写天如:8
"%W" 或者 "%w"表示周(全年52周) 如:05(全年第五周)
"%H"表示完整小时如:08
"%h"表示简写小时如:8
"%N"表示完整分钟如:08
"%n"表示简写分钟如:8
"%S"表示完整秒如:08
"%s"表示简写秒如:8
"%K"表示完整毫秒如:008
"%04X"表示生成单据号的流水号
- 04表示4为数字如:流水号0005
- % X 表示通配符
其他非特定关键字可以任意组合

@TableName ---为单据号的数据库表
@ColName ----为数据表的单据号列
@Remove -----为剔除自动生成单据号的数组如:格式为"1,2,3,4,5,"
@Mantissa ----为控制是否剔除包含还是匹配;"1"表示包含剔除的数据；"0"表示剔除完全匹配的单据号
@Where ---为自动生成单据号的查询条件
@ZD_date ---为单据号生成的单据日期
@SSN ----为生产的单据号

例子:

格式字符串：投诉单%Y%M%06XAAAsdsA

自动生成的单据号：投诉单201206000001AAAsdsA

View Code

Create PROCEDURE [dbo].[ProcessAutoSN]
(@SJ nvarchar(500),
@TableName nvarchar(500),
@ColName nvarchar(500),
@Remove nvarchar(500),
@Mantissa bit,
@Where nvarchar(500),
@ZD_date DATETIME ,
@SSN nvarchar(500) OUTPUT)
AS
BEGIN 
/*
--自动生成单据号
--@SJ 流水号格式字符串
--@TableName 表名
--@ColName 列名
declare 
@SJ nvarchar(500),
@TableName nvarchar(500),
@ColName nvarchar(500),
@SSN nvarchar(500)
set @TableName='Table1'
set @ColName='id'--投诉单1009001
set @SJ='投诉单%Y%M%04XAAAsdsA'
*/
declare @SN nvarchar(500),
@Year nvarchar(4),@MinYear nvarchar(2),
@Month nvarchar(2),@Day nvarchar(2),@Hour nvarchar(2),@Minute nvarchar(2),@week nvarchar(2),
@second nvarchar(2),@Msecond nvarchar(2),@Sp nvarchar(500),@int int,
@SL nvarchar(500),@Zero nvarchar(500),@ZZZ nvarchar(500),@Xzero INT
IF (@ZD_date IS NULL)
SET @ZD_date=GETDATE()
set @SN=@SJ
set @Year=year(convert(nvarchar(10),@ZD_date,120))
set @MinYear=right(year(convert(nvarchar(10),@ZD_date,120)),2)
set @Month=month(convert(nvarchar(10),@ZD_date,120))
set @Day=Day(convert(nvarchar(10),@ZD_date,120))
SET @week=CEILING( DATEDIFF(d,CONVERT(NVARCHAR(4),YEAR(@ZD_date))+'-01-01',@ZD_date)/7.00)
set @Hour=right('00'+convert(nvarchar(2),datepart(Hh,@ZD_date)),2)
set @Minute=right('00'+convert(nvarchar(2),datepart(mi,@ZD_date)),2)
set @second=right('00'+convert(nvarchar(2),datepart(ss,@ZD_date)),2)
set @Msecond=right('00'+convert(nvarchar(4),datepart(ms,@ZD_date)),4)
set @SN=replace(@SN COLLATE Chinese_PRC_CS_AS,'%Y',@Year)
set @SN=replace(@SN COLLATE Chinese_PRC_CS_AS,'%y',@MinYear)
set @SN=replace(@SN COLLATE Chinese_PRC_CS_AS,'%m' ,@Month)
set @SN=replace(@SN COLLATE Chinese_PRC_CS_AS,'%M' ,right('00'+convert(nvarchar(2),@Month),2))
set @SN=replace(@SN COLLATE Chinese_PRC_CS_AS,'%D',right('00'+convert(nvarchar(2),@Day),2))
set @SN=replace(@SN ,'%d',@Day)


set @SN=replace(@SN COLLATE Chinese_PRC_CS_AS,'%w',right('00'+convert(nvarchar(2),@week),2))
set @SN=replace(@SN COLLATE Chinese_PRC_CS_AS,'%W',right('00'+convert(nvarchar(2),@week),2))

set @SN=replace(@SN COLLATE Chinese_PRC_CS_AS,'%H',right('00'+convert(nvarchar(2),@Hour),2))
set @SN=replace(@SN,'%h',@Hour)
set @SN=replace(@SN COLLATE Chinese_PRC_CS_AS,'%N',right('00'+convert(nvarchar(2),@Minute),2))
set @SN=replace(@SN,'%n',@Minute)
set @SN=replace(@SN COLLATE Chinese_PRC_CS_AS,'%S',right('00'+convert(nvarchar(2),@second),2))
set @SN=replace(@SN,'%s',@second)
set @SN=replace(@SN,'%K',right('000'+convert(nvarchar(2),@Msecond),3))
set @Zero=patindex('%[%]%X%',@SN)
SET @Xzero=@Zero
if (@Zero<=0)
begin
SET @SSN=@SN
return
end
set @Sp=right(@SN,len(@SN)+1-charIndex('%',@SN))
set @SL=left(@Sp,charindex('X',@Sp))
set @ZZZ=@Sp
if (charIndex('X',@Sp)<=0)
begin
SET @SSN=@SN
return
end
set @Sp=left(@Sp,charIndex('X',@Sp)-1)
set @Sp=replace(@Sp,'%','')
if(isnumeric(@Sp)=0)
begin
SET @SSN=''
return
end
IF Not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].['+@TableName+']') AND type in (N'U'))
BEGIN
SET @SSN=@SN
return 
end
else
begin
 IF Not EXISTS (select [name] from syscolumns   where  
 [name]=@ColName   and   OBJECTPROPERTY(id,'IsUserTable')=1   and  
 object_name(id)=@TableName)
begin
SET @SSN=@SN
return 
end
end
declare @SQL nvarchar(4000),@Max nvarchar(500),@Start int,@End int 
set @Zero=Replicate('0',@Sp)
set @Start=charIndex('%',@SN)-1
set @End=charIndex('X',@SN)
SET @End=@End+@Sp-(@End-@Start)
--SELECT @Xzero
--set @SN=replace(@SN,@SL,@Zero)
set @SQL='left(right('+@ColName+',len('+@ColName+')-'+convert(nvarchar(50),@Start)+'),'+convert(nvarchar(50),@Sp)+')'
set @SQL='select @Max=max(case when isnumeric('+@SQL+')=1 then '+@SQL+'else '''+@Zero+''' end ) from '+@TableName+' where len('+@ColName+')=len(replace('''+@SN+''','''+@SL+''','''+@Zero+'''))'
set @SQL=@SQL+' and left('+@ColName+','+convert(nvarchar(50),@Start)+')=left(replace('''+@SN+''','''+@SL+''','''+@Zero+'''),'+convert(nvarchar(50),@Start)+')'
set @SQL=@SQL+' and left('+@ColName+','+convert(nvarchar(50),@Xzero-1)+')+right('+@ColName+',len('+@ColName+')-'+convert(nvarchar(50),@Xzero-1+LEN(@Zero))+')=replace('''+@SN+''','''+@SL+''','''+''+''')'

--set @SQL=@SQL+' and right('+@ColName+',len('+@ColName+')-'+convert(nvarchar(50),@Xzero-1)+')=right(replace('''+@SN+''','''+@SL+''','''+@Zero+'''),len(replace('''+@SN+''','''+@SL+''','''+@Zero+'''))-'+convert(nvarchar(50),@End)+')'
IF (@Where<>'' AND @Where IS NOT NULL)
SET @sql=@sql+' and '+@Where

--SELECT @sql

EXEC sp_executesql @sql, N'@Max nvarchar(500) OUTPUT', @Max OUTPUT 
if (@Max is null)
set @Max=0
set @Start=convert(int,@Max)+1
if (@Remove is not null and ltrim(@Remove)<>'')
begin 
if (@Mantissa is not null and @Mantissa=0)
begin
while(dbo.CheckArrInStr(' '+convert(nvarchar(50),@Start),@Remove,',')=1)
begin
set @Start=@Start+1
end
end
else
begin
while(dbo.CheckArrInStr(' '+right(convert(nvarchar(50),@Start),1),@Remove,',')=1)
begin
set @Start=@Start+1
end
end
end

set @SN=replace(@SN,left(@SL,charindex('X',@SL)),right(@Zero+convert(nvarchar(500),@Start),len(@Zero)))
SET @SSN=@SN
END

检查完全匹配剔除数据

View Code

Create function [dbo].[CheckArrInStr](@s as varchar(500),@sArr as varchar(5000),@Split varchar(500)) 
returns int
as 
begin
 declare @next int  
 declare @ret int
 set @ret=0
 set @next=1
 while @next<=dbo.Get_StrArrayLength(@sArr,@Split)
 begin
if (ltrim(dbo.Get_StrArrayStrOfIndex(@sArr,@Split,@next))<>'')
begin
   if (charindex(dbo.Get_StrArrayStrOfIndex(@sArr,@Split,@next),@s)>0)
  begin
  set @ret=1;
  break;
  end
end
   set @next=@next+1
 end

 return @ret
end

检查包含剔除数据

View Code

Create function [dbo].[CheckStrInArr](@s as varchar(50),@sArr as varchar(5000),@Split varchar(500)) returns int
as 
begin
 declare @next int  
 declare @ret int
 set @ret=0
 set @next=1
 while @next<=dbo.Get_StrArrayLength(@sArr,@Split)
 begin
   if dbo.Get_StrArrayStrOfIndex(@sArr,@Split,@next)=@s 
  begin
  set @ret=1;
  break;
  end
   set @next=@next+1
 end
 return @ret
end

字符串分割长度

View Code

Create function [dbo].[Get_StrArrayLength]
(
  @str varchar(5000),  --要分割的字符串
  @split varchar(10)  --分隔符号
)
returns int
as
begin
  declare @location int
  declare @start int
  declare @length int

  set @str=ltrim(rtrim(@str))
  set @location=charindex(@split,@str)
  set @length=1
  while @location<>0
  begin
    set @start=@location+1
    set @location=charindex(@split,@str,@start)
    set @length=@length+1
  end
  return @length
end

取分割数组数据

View Code

Create function [dbo].[Get_StrArrayStrOfIndex]
(
  @str varchar(5000),  --要分割的字符串
  @split varchar(10),  --分隔符号
  @index int --取第几个元素
)
returns varchar(5000)
as
begin
  declare @location int
  declare @start int
  declare @next int
  declare @seed int

  set @str=ltrim(rtrim(@str))
  set @start=1
  set @next=1
  set @seed=len(@split)
  
  set @location=charindex(@split,@str)
  while @location<>0 and @index>@next
  begin
    set @start=@location+@seed
    set @location=charindex(@split,@str,@start)
    set @next=@next+1
  end
  if @location =0 select @location =len(@str)+1 
  
  return substring(@str,@start,@location-@start)
end