create view v_getdate
as
select getdate() [output]
go
---------------------------------------------------------------
create function f_getdate()
returns varchar(50)
as
begin
declare @n datetime,@a varchar(50)
select @n = output from v_getdate
select @a='PK'+convert(char(8),@n,112)+right(10001+isnull(max(字段),0),4) from 表 where datediff(day,时间,@n)=0
return(@a)
end
go
-----------------------------------------------------------------你在你的表里的那个字段的默认值里写:
dbo.getNumber()
as
select getdate() [output]
go
---------------------------------------------------------------
create function f_getdate()
returns varchar(50)
as
begin
declare @n datetime,@a varchar(50)
select @n = output from v_getdate
select @a='PK'+convert(char(8),@n,112)+right(10001+isnull(max(字段),0),4) from 表 where datediff(day,时间,@n)=0
return(@a)
end
go
-----------------------------------------------------------------你在你的表里的那个字段的默认值里写:
dbo.getNumber()
create function f_getid()
returns varchar(14)
as
begin
declare @id varchar(14),@dt varchar(10)
select @dt=dt from v_getdate
select @id=max(id) from tb where id like @dt+'%'
if @id is null
set @id=@dt+'0001'
else
set @id=@dt+right('0000'+cast(cast(right(@id,4) as int)+1 as varchar),4)
return(@id)
end
go--创建表
create table tb(id varchar(20) default dbo.f_getid() primary key,name varchar(10))
go--创建视图,得到当前日期(因为函数中不能使用getdate())
create view v_getdate as select dt='RK'+convert(varchar,getdate(),112)
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb
go--删除环境
drop table tb
drop view v_getdate
drop function f_getid