写一个自动编号的存储过程

Create Function f_getid()
Returns Varchar(13)
As
Begin
 Declare @id varchar(13),@dt varchar(8)
 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,3) 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=convert(varchar,getdate(),012)
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('张十')
insert into tb(name) values('张十一')
insert into tb(name) values('张十二')

--显示插入的结果
select * from tb

go

id                   name      
-------------------- ----------
0411040001           张三
0411040002           张四
0411040003           张五
0411040004           张六
0411040005           张七
0411040006           张八
0411040007           张九
0411040008           张十
0411040009           张十一
0411040010           张十二

--删除环境
drop table tb
drop view v_getdate
drop function f_getid
                                                                                                          author :miss wang reshipment

转载于:https://www.cnblogs.com/wisdom/archive/2006/12/31/608601.html