create procedure p
@number varchar(20) output
as
declare @Port varchar(5) --5位字符
,@Flag varchar(1) --E或I
,@year int --年
select @Port=left(newid(),5)
,@Flag='F'
,@year=year(getdate())if exists(select 1 from 表)
begin
declare @max varchar(6)
select @max=max(right(EIRNO,6)) from 表 select @number=@Port
+@Flag
+convert(varchar,@year)
+convert(varchar,(convert(int,@max)+1))
end
else
begin
select @number=@Port
+@Flag
+convert(varchar,@year)
+'000001'
end
go
@number varchar(20) output
as
declare @Port varchar(5) --5位字符
,@Flag varchar(1) --E或I
,@year int --年
select @Port=left(newid(),5)
,@Flag='F'
,@year=year(getdate())if exists(select 1 from 表)
begin
declare @max varchar(6)
select @max=max(right(EIRNO,6)) from 表 select @number=@Port
+@Flag
+convert(varchar,@year)
+convert(varchar,(convert(int,@max)+1))
end
else
begin
select @number=@Port
+@Flag
+convert(varchar,@year)
+'000001'
end
go
@port varchar(5),
@flag varchar(2),
@year int)
returns varchar(14)
as
begin
declare @no varchar(6)
set @no=(select max(right(eirno,6)) from eir) if @no is null
set @no='0' set @no=right(cast(cast(@no as int) + 1000001 as varchar(7)),6)
return @port+@flag+ltrim(str(@year))+@no
end
go
select dbo.f_1('wgsad','e',12)
------------------------------------------------------------------------------
create function f_getsrl(@Port char(5),@Flag char(1),@Year char(2))
returns char(14)
as
begin
declare @srl char(14)
select @srl = max(EIRNO) from EIR where left(EIRNO,8) = @Port+@Flag+@Year
if @ret is null
set @srl = @Port+@Flag+@Year+'000001'
else
set @srl = @Port+@Flag+@Year+right('000000'+cast((right(@srl,6)+1) as varchar(6)),6)
return @srl
end
returns char(14)
as
begin
declare @srl char(14)
select @srl = max(EIRNO) from EIR where left(EIRNO,8) = @Port+@Flag+@Year
if @srl is null
set @srl = @Port+@Flag+@Year+'000001'
else
set @srl = @Port+@Flag+@Year+right('000000'+cast((right(@srl,6)+1) as varchar(6)),6)
return @srl
end
@Port varchar(5)=null, --5位字符
@Flag varchar(1)=null, --E或I
@year int=null , --年
@number varchar(20) output --新编号
as
declare
select @Port=isnull(@Port,left(newid(),6))
,@Flag=isnull(@Flag,'E')
,@year=isnull(@year,year(getdate()))if exists(select 1 from 表)
begin
declare @max varchar(6)
select @max=max(right(EIRNO,6)) from 表 select @number=@Port
+@Flag
+right(convert(varchar,@year),2)
+convert(varchar,(convert(int,@max)+1))
end
else
begin
select @number=@Port
+@Flag
+convert(varchar,@year)
+'000001'
end
go
create function f_1(
@port varchar(5),
@flag varchar(2),
@year int)
returns varchar(14)
as
begin
declare @no varchar(6)
set @no=(select max(right(eirno,6)) from eir with (TABLOCK)) if @no is null
set @no='0' set @no=right(cast(cast(@no as int) + 1000001 as varchar(7)),6)
return @port+@flag+ltrim(str(@year))+@no
end
go
select dbo.f_1('wgsad','e',12)
( 如果取编号和存储数据是分两步的话, 必须把取编号和存储数据放在一个事务中,
否则select 完成后锁就释放了, 就达不到防止编号重复的作用)begin tran
declare @bh varchar(14)
select @bh=dbo.f_1('wgsad','e',12)
insert 表(编号,...) values(@bh,....)
-- 其他处理...
commit tran