字符串自动表示列(组合自动表示列)

代码如下：

------先定义一张表

--创建测试表
create table tb
(
 id char(5) not null,
 name varchar(10),
 pkey varchar(2)
)
go

alter table tb add constraint pk_tb primary key(id)
go

-----先自定义一个组合函数(产生组合标识列)

--自定义函数组合标识
create function f_getid(@no varchar(2))
returns char(3)
as
begin
 declare @id int
 if not exists(select 1 from tb where id=@no+'001')
  set @id=1
 else
  begin
  select @id=max(substring(id,2,6)) from tb where id like @no+'%'
  if @id is null
  set @id=1
  else
   begin
    declare @id1 int
    select @id1=min(substring(id,2,6)) from tb a where substring(id,2,6)<>@id and not exists(select 1 from tb where id=substring(a.id,2,6)+1 and id like @no+'%') and id like @no+'%'
    if @id1 is not null set @id=@id1
    set @id=@id+1
   end
end
lb_re:
 return(right('000'+cast(@id as varchar),3))
end
go

---最后定义一个存储过程

create procedure pro_id
 @keyid varchar(2),
 @name varchar(10)
as
declare @id varchar(5)
set @id=@keyid+dbo.f_getid(@keyid)
print @id
insert into tb(id,name,pkey) values(@id,@name,@keyid)
go

exec pro_id @keyid='03',@name='iieeddi'
go