SQL生成流水号

if object_id('up_getBaseNo') is not null
    begin
        drop procedure up_getBaseNo
    end
go 
alter proc [dbo].[up_getBaseNo](
  @hid char(6)--酒店id
    , @as_baseName varchar(30)
    , @as_other varchar(30) = ''--取消号的其它信息，例如按日期取号的话，这里传日期的字符串，不能为null
    , @no47 tinyint = 0 --不含4和7
    , @len tinyint = 6 --返回长度，前面补零，但最大长度只支持8位
)
as
--set nocount on
/*******************************************************************************************
名称：取流水号值
说明：取流水号值
作者：向以胜
日期：2021-02-05
[up_getBaseNo] @hid = '000003',@as_baseName = 'res',@as_other = '2016-05-23',
[up_getBaseNo] @hid = '000002',@as_baseName = 'hotel',@as_other = '2016-05-22',@len = 0,@no47 = 1
select * from seed
--取号种子表
create table [dbo].[seed](
    [id] [uniqueidentifier] primary key not null,
    [hid] [char](6) not null,
    [name] [varchar](30) not null,
    [value] [int] null,
    [other] [varchar](30) null
)
********************************************************************************************/
declare @value varchar(10)
set @as_other = ISNULL(@as_other,'')
update seed set value = case when value < 100000001 or value > 199999999 then 100000001 else value + 1 end where hid = @hid and name = @as_baseName and other = @as_other
--如果没有插入一条新记录
if @@rowcount = 0
begin
    insert into seed(id,hid,name,other,value)values(newid(),@hid,@as_baseName, @as_other ,100000001)
    --delete seed where hid = @hid and name = @as_baseName and isnull(other,'') <> @as_other
end
declare @value_ori varchar(10)  
select @value = value from seed where hid = @hid and name = @as_baseName and isnull(other,'') = @as_other
set @value_ori = @value
if @no47 = 1
begin
    if RIGHT(@value,1) = '4' or RIGHT(@value,1) = '7' set @value = @value + 1
    if substring(@value,8,1) = '4' or substring(@value,8,1) = '7' set @value = @value + 10
    if substring(@value,7,1) = '4' or substring(@value,7,1) = '7' set @value = @value + 100
    if substring(@value,6,1) = '4' or substring(@value,6,1) = '7' set @value = @value + 1000
    if substring(@value,5,1) = '4' or substring(@value,5,1) = '7' set @value = @value + 10000
    if substring(@value,4,1) = '4' or substring(@value,4,1) = '7' set @value = @value + 100000
    if substring(@value,3,1) = '4' or substring(@value,3,1) = '7' set @value = @value + 1000000
    if substring(@value,2,1) = '4' or substring(@value,2,1) = '7' set @value = @value + 10000000
    if @value_ori <> @value  
    begin
  update seed set value = @value where hid = @hid and name = @as_baseName and isnull(other,'') = @as_other
    end
end
--将other中的-去掉后拼接在前面一起返回。
declare @return varchar(10)
if @len = 0
    set @return = convert(varchar(10) , convert(int,RIGHT(@value,8) ))
else
    set @return = right(@value,@len)
select replace(@as_other,'-','') + @return as seqno
return