把最大的编号存放到指定表中,然后每次都从这个表中去取数。set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[SOF_getmaxbh] @biaoshi varchar(3), --单据类型标识 @addflag integer, --0,1 在前台使用;2 组成一个号返回,序号不加1(结转号),3 序号加 1 返回(单据编号); @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_SOF_getmaxbh else save tran tran_SOF_getmaxbh
if @addflag=1 or @addflag=3 begin
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
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(11) set @s_recnum=LTRIM(str(@recnum))
if @addflag>1 set @maxbh=@biaoshi+ REPLICATE('0',11-len(@biaoshi)-len(@s_recnum))+@s_recnum else select @biaoshi+ REPLICATE('0',11-len(@biaoshi)-len(@s_recnum))+@s_recnum as recnum
--结束事务处理 if @tran_point=0 commit tran tran_SOF_getmaxbh goto return_lab err_lab: if @return<100 set @return=@errcode +@return rollback tran tran_SOF_getmaxbh return_lab: return @return
可以适当简化成这样: Create procedure [dbo].[getIDonly] ( @name varchar(2), @re varchar (50) output ) AS BEGIN begin TRANSACTION declare @GD varchar(15) --固定左边 set @GD= @name+ convert(varchar(8),GETDATE(),112)
declare @DT varchar(5) ---动态右边 set @DT=(select right (left( max(CAST ( RIGHT (id,5) as NUMERIC(20,0))*100001+1),10),5) from test where id like @name +'%' )
if @DT is null begin set @DT='00001' end
if @@error<>0 begin rollback tran end commit tran set @re= @GD+@DT END
CREATE FUNCTION f_NextBH() RETURNS char(8) AS BEGIN RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK)) END GO用 这种函数 会存在上诉问题么
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SOF_getmaxbh]
@biaoshi varchar(3), --单据类型标识
@addflag integer, --0,1 在前台使用;2 组成一个号返回,序号不加1(结转号),3 序号加 1 返回(单据编号);
@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_SOF_getmaxbh
else
save tran tran_SOF_getmaxbh
if @addflag=1 or @addflag=3
begin
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
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(11)
set @s_recnum=LTRIM(str(@recnum))
if @addflag>1
set @maxbh=@biaoshi+ REPLICATE('0',11-len(@biaoshi)-len(@s_recnum))+@s_recnum
else
select @biaoshi+ REPLICATE('0',11-len(@biaoshi)-len(@s_recnum))+@s_recnum as recnum
--结束事务处理
if @tran_point=0
commit tran tran_SOF_getmaxbh
goto return_lab err_lab:
if @return<100 set @return=@errcode +@return
rollback tran tran_SOF_getmaxbh return_lab:
return @return
可以适当简化成这样:
Create procedure [dbo].[getIDonly]
(
@name varchar(2),
@re varchar (50) output
)
AS
BEGIN
begin TRANSACTION
declare @GD varchar(15) --固定左边
set @GD= @name+ convert(varchar(8),GETDATE(),112)
declare @DT varchar(5) ---动态右边
set @DT=(select right (left( max(CAST ( RIGHT (id,5) as NUMERIC(20,0))*100001+1),10),5) from test where id like @name +'%' )
if @DT is null
begin
set @DT='00001'
end
if @@error<>0
begin
rollback tran
end
commit tran
set @re= @GD+@DT
END
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO用 这种函数 会存在上诉问题么