请问当我插入一行数据的时候 让这一行数据的某一列自动增长怎么写存储过程？
就是我有一个字段是aa 这个字段是nvarchar型的 默认值是abc200300
每次插入一条数据 这个字段就变成abc200301 abc200302 abc200303...
[解决办法]
create table wos
(aa nvarchar(20) constraint df_aa default('abc200300')
)
create proc sp_addwos
as
begin
set nocount on
declare @ta int,@maxaa nvarchar(20)
select @maxaa=max(aa) from wos
if @maxaa is null
begin
insert into wos(aa) values('abc200301')
end
else
begin
select @ta=cast(right(@maxaa,2) as int)+1
insert into wos(aa) select left(@maxaa,7)+replicate('0',2-len(@ta))+rtrim(@ta)
end
end
-- 测试
exec sp_addwos
select aa from wos
/*
aa
--------------------
abc200301
(1 row(s) affected)
*/
exec sp_addwos
go 10
select aa from wos
/*
aa
--------------------
abc200301
abc200302
abc200303
abc200304
abc200305
abc200306
abc200307
abc200308
abc200309
abc200310
abc200311
(11 row(s) affected)
*/
CREATE TABLE [dbo].[maxbh](
[biaoshi] [char](3) COLLATE Chinese_PRC_CI_AS NOT NULL,
[maxbh] [char](11) COLLATE Chinese_PRC_CI_AS NULL,
[recnum] [int] NULL,
[mkbh] [char](2) COLLATE Chinese_PRC_CI_AS NULL
CONSTRAINT [PK_maxbh] PRIMARY KEY CLUSTERED
(
[biaoshi] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--exec Get_maxbh 'aaa',1
ALTER PROCEDURE [dbo].[Get_maxbh]
@biaoshi varchar(3), --单据类型标识
@addflag integer,
@maxbh varchar(11)=NULL output --直接返回编号
AS
--内部变量声明
declare @recnum integer,
@rowcount integer
set @recnum=0
/*非事务内容执行*/
--每一过程拥有一唯一区界号，过程内的异常编号在此基础上增加
declare @errcode integer
set @errcode=1800
declare @return integer
set @return=0
--启动事务处理
declare @tran_point int--控制事务嵌套
set @tran_point=@@trancount --保存事务点
if @tran_point=0
begin tran tran_Get_maxbh
else
save tran tran_Get_maxbh
set @addflag =1
if @addflag=1 or @addflag=3
begin
--增加isnull(recnum,0)处理
update maxbh set @recnum=recnum=isnull(recnum,0)+1 where biaoshi=@biaoshi
set @rowcount=@@rowcount
if @@error<>0
begin
set @return=1
goto err_lab
end
end
else
begin
--增加isnull(recnum,0)处理
select @recnum=isnull(recnum,0) from maxbh(nolock) where biaoshi=@biaoshi
end
if @rowcount=0
begin
set @recnum=0
insert into maxbh (biaoshi,maxbh,mkbh,recnum) values (@biaoshi,'','',@recnum)
end
--返回结果
declare @s_recnum varchar(14)
set @s_recnum=LTRIM(str(@recnum))
if @addflag>1
set @maxbh=@biaoshi+ REPLICATE('0',14-len(@biaoshi)-len(@s_recnum))+@s_recnum
else
select @biaoshi+ REPLICATE('0',14-len(@biaoshi)-len(@s_recnum))+@s_recnum as recnum
--结束事务处理
if @tran_point=0
commit tran tran_Get_maxbh
goto return_lab
err_lab:
if @return<100 set @return=@errcode +@return
rollback tran tran_Get_maxbh
return_lab:
return @return
create table tab4
(aa nvarchar(50) default 'abc200300',num int)
insert into tab4 values( 'abc200304',5)
select * from tab4
delete tab4 where num in(2,3,4,5)
declare @aa nvarchar(20)
set @aa=
(
select aa from (
select top 10 ROW_NUMBER()over(order by id desc)aid, 'abc'+aa as aa from
(
select ROW_NUMBER() over(order by aa )id , convert(varchar(20),(stuff(aa,1,3,'')+1)) aa,num from tab4
)b
)c where aid=1
)
insert into tab4 values( @aa,3)
aa num
-------------------------------------------------- -----------
abc200300 1
abc200301 2
abc200302 3
abc200303 3
abc200304 3
abc200305 3
abc200306 3
abc200307 3
abc200308 3
