请问当我插入一行数据的时候 让这一行数据的某一列自动增长怎么写存储过程？
就是我有一个字段是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

[解决办法]

引用:

引用:SQL code?1234567891011121314151617181920212223242526272829create table tab4(aa nvarchar(50) default 'abc200300',num int)insert into tab4 values( 'abc200304',5)select……

你插入数据的时候可以改