十六进制.sql

sqlserver 数据库编程的绝好脚本

--创建自定义函数,得到新的ID
create function f_getid()
returns varchar(12)
as
begin
	declare @id varchar(12)
	declare @st varchar(16),@id1 varchar(1),@id2 varchar(1),@i int

	set @st='0123456789ABCDEF'
	select @id=max(主键) from tb

	if @id is null 
		set @id='03000001-P01'
	else
		select @id1=substring(@id,11,1),@id2=right(@id,1)
			,@id=left(@id,10)
			,@i=charindex(@id2,@st)
			,@id2=case @i when 16 then '0' else substring(@st,@i+1,1) end
			,@id1=case @i when 16 then substring(@st,charindex(@id1,@st)+1,1)
				else @id1 end
			,@id=@id+@id1+@id2
return(@id)
end
go

--创建测试表
create table tb(主键 varchar(12) not null primary key default dbo.f_getid()
		,aa int)

--插入数据测试
insert into tb(aa) values(1)
insert into tb(aa) values(2)
insert into tb(aa) values(3)
insert into tb(aa) values(4)
insert into tb(aa) values(5)
insert into tb(aa) values(6)
insert into tb(aa) values(7)
insert into tb(aa) values(8)
insert into tb(aa) values(9)
insert into tb(aa) values(10)
insert into tb(aa) values(11)
insert into tb(aa) values(12)
insert into tb(aa) values(13)
insert into tb(aa) values(14)
insert into tb(aa) values(15)
insert into tb(aa) values(16)
insert into tb(aa) values(17)
insert into tb(aa) values(18)

--显示结果
select * from tb

go
--删除测试环境
drop table tb
drop function f_getid