34,838
社区成员




--drop table tes
--go
--建表
create table tes (coding varchar(20) not null,a int)
go
--新建个函数
--drop function [dbo].[AutoGetCoding]
create function [dbo].[AutoGetCoding](@date datetime)
returns varchar(5)
as
begin
declare @str varchar(5)
select @str=isnull(max(coding),'A0000')
from tes
select @str=left(@str,1)+right('0000'+convert(varchar(4),convert(int,right(@str,4))+1),4)
return (@str)
end
--把表的默认值设置成[dbo].[AutoFollowExamCode](getdate())
--测试
go
--加约束
ALTER TABLE dbo.tes ADD CONSTRAINT
DF_t_coding DEFAULT [dbo].[AutoGetCoding](getdate()) FOR coding
--删除测试数据
go
--
declare @i int
select @i=8
while @i>0
begin
insert into tes(a) values ('1')
select @i=@i-1
end
--显示结果
go
select * from tes
/*coding a
-------------------- -----------
A0001 1
A0002 1
A0003 1
A0004 1
A0005 1
A0006 1
A0007 1
A0008 1
*/
CREATE TABLE [dbo].[tb1] (
[autoid] [int] IDENTITY (1, 1) NOT NULL ,
[myid] AS (right(str((10000 + [autoid])),4)) ,
[mytext] [char] (10)
)
insert into tb1 (mytext) values('qqqq')
insert into tb1 (mytext) values('bbbbb')
insert into tb1 (mytext) values('fffff')
insert into tb1 (mytext) values('tttt')
select * from tb1
/*
autoid myid mytext
----------- -------- ----------
1 0001 qqqq
2 0002 bbbbb
3 0003 fffff
4 0004 tttt
(所影响的行数为 4 行)
*/