系统自动编号

create table t_asset(
id int,
assetcode varchar(50) null,
company varchar(50) null,
ctype varchar(50) null
)

insert into t_asset 
values(1,'HTSK-JX-0001','HTSK','JX'),
      (2,'HTSK-JX-0002','HTSK','JX'),
      (3,'HTSK-JX-0003','HTSK','JX'),
      (4,'HTSK-DQ-0001','HTSK','DQ'),
      (5,'','HTSK','DQ'),
      (6,'','HTSK','JX'),
      (7,'','HTJT','DQ'),
      (8,'','HTJT','JX'),
      (9,'','HTSK','JZ')


SELECT * from t_asset

________________________________________________________________________
--循环编号
declare @id int
declare @company varchar(100)
declare @ctype varchar(100)
declare @companyctype varchar(100)
declare @sql nvarchar(max)

declare Autocursor scroll cursor for
SELECT id,company,ctype from t_asset where assetcode=''
open Autocursor
fetch first from Autocursor
into @id,@company,@ctype
while @@FETCH_STATUS=0
begin

set @sql='select @assetcode='''+@company+'-'+@ctype+'-''+RIGHT(''000''+convert(varchar(50),ISNULL(max(RIGHT(assetcode,4)),0)+1),4) from t_asset where assetcode like ''%'+@company+'-'+@ctype+'-%'''
exec sp_executesql @sql,N'@assetcode nvarchar(200) out',@companyctype out
update t_asset set assetcode=@companyctype where id=@id

fetch next from Autocursor
into @id,@company,@ctype
end
CLOSE Autocursor
DEALLOCATE Autocursor