DECLARE @TB TABLE([spbm] VARCHAR(6)) --INSERT @TB --SELECT 'p00999'SELECT ISNULL((SELECT 'p'+RIGHT('00000'+RTRIM(CAST(RIGHT(spbm,5) AS INT)+1),5) FROM @TB),'p00001')
你这个编号是主建的话 做成触发器不太合适。 可以写一个小存储过程或者函数,每次在追加数据之前调用,并返回最新的编号,然后再追加到你的表里。如, create function fun
return varchar(6) as begin declare @code varchar(6); select @code = max(spbm) from table; if @code =null begin @code = "p00001"; end else begin set @code='P'+replicate('0',5-len(@code))+ (Cast(substring(@code,2,len((@code)-1)as int)+1) ; end return @code; end
同样类型的可以示范下。 楼主那个更简单。 /********************************************* 2009022660001 Select dbo.F_OAGetNewRNo(Cast('2009-02-01' as smalldatetime)) ************************************************************/ ALTER FUNCTION [dbo].[F_OAGetNewCRNo] (@InDate smalldatetime) RETURNS varchar(20) AS BEGIN Declare @Rst varchar(20),@MaxNo int ,@CurrentDate varchar(20) Select @Rst = '',@MaxNo = 0
--取得当前日期字符串 Select @CurrentDate = Cast(year(@InDate) as varchar) + --年 right('00' + cast(month(@InDate) as varchar),2) + -- 月 right('00' + cast(day(@Indate) as varchar),2) --取得数据库中当月的最大一票 Select @MaxNo = max(cast(right(fCRNo,4) as int)) From TOA_CarRepair Where year(@InDate) = cast(left(fCRNo,4) as int) and month(@InDate) = Cast(substring(fCRNo,5,2) as int) and day(@InDate) = cast(substring(fCRNo,7,2) as int)
if (isnull(@MaxNo,0) >0) Begin select @Rst = @CurrentDate + right('000' + cast(@MaxNo + 1 as varchar),4) End else Select @Rst = @CurrentDate + '0001' RETURN @Rst END GO
用个函数,每一位都可以做到按照你需要的进制来做。Create Function [dbo].[GetNextSeqNo_DEC](@CurrentSeqNo varchar(6),@SN_SeqLen int) Returns varchar(6) As ------------------------------------------------------------------------------------------------- ----*Programer*: Zoe ----*Date*: 2009/05/11 -----Purpose: 输入当前的流水号和长度(有四位,五位,六位),按照10进制返回下一个流水号,长度与原来的一样. ------------------------------------------------------------------------------------------------- Begin Declare @iNewSeqNo int Declare @CurrentSeqNo_Int int Declare @NewSeqNo_Str varchar(6) Declare @iNewSeqNo_Str varchar(6) ------------------------------------------------------------------------------------------------- set @CurrentSeqNo_Int=Convert(int,@CurrentSeqNo) ----将传进来的字符转换成整型 set @iNewSeqNo=@CurrentSeqNo_Int+1 ----获得下一个流水号,只需加一(十进制) set @iNewSeqNo_Str=Convert(varchar(6),@iNewSeqNo) ----将下一个流水号转换成字符型 ------------------------------------------------------------------------------------------------- ----将@iNewSeqNo_Str初始化为6位的字符串 If len(@iNewSeqNo_Str)=1 set @iNewSeqNo_Str='00000'+@iNewSeqNo_Str else If len(@iNewSeqNo_Str)=2 set @iNewSeqNo_Str='0000'+@iNewSeqNo_Str else If len(@iNewSeqNo_Str)=3 set @iNewSeqNo_Str='000'+@iNewSeqNo_Str else If len(@iNewSeqNo_Str)=4 set @iNewSeqNo_Str='00'+@iNewSeqNo_Str else If len(@iNewSeqNo_Str)=5 set @iNewSeqNo_Str='0'+@iNewSeqNo_Str ------------------------------------------------------------------------------------------------- ----按照要求传出流水号,有的流水号是四位,有的是五位。 set @NewSeqNo_Str=right(@iNewSeqNo_Str,@SN_SeqLen) Return(@NewSeqNo_Str) ------------------------------------------------------------------------------------------------- End ------------------------------------------------------------------------------------------------- --select [dbo].[GetNextSeqNo_DEC]('9999','4') --select [dbo].[GetNextSeqNo_DEC]('09999','5') --select [dbo].[GetNextSeqNo_DEC]('009999','6')
p00999那么新添的编号为p01000
给你一个思路。
1.首先查询出 最大的编号 select max
2.把它转换为INT 比如 p00999转换999
3.转换后的INT数据+1=1000
4.再1000转换为p01000
DECLARE @TB TABLE([spbm] VARCHAR(6))
--INSERT @TB
--SELECT 'p00999'SELECT ISNULL((SELECT 'p'+RIGHT('00000'+RTRIM(CAST(RIGHT(spbm,5) AS INT)+1),5) FROM @TB),'p00001')
可以写一个小存储过程或者函数,每次在追加数据之前调用,并返回最新的编号,然后再追加到你的表里。如,
create function fun
return varchar(6)
as
begin
declare @code varchar(6);
select @code = max(spbm) from table;
if @code =null
begin
@code = "p00001";
end
else
begin
set @code='P'+replicate('0',5-len(@code))+ (Cast(substring(@code,2,len((@code)-1)as int)+1) ;
end
return @code;
end
楼主那个更简单。
/*********************************************
2009022660001
Select dbo.F_OAGetNewRNo(Cast('2009-02-01' as smalldatetime))
************************************************************/
ALTER FUNCTION [dbo].[F_OAGetNewCRNo] (@InDate smalldatetime)
RETURNS varchar(20)
AS
BEGIN
Declare @Rst varchar(20),@MaxNo int ,@CurrentDate varchar(20)
Select @Rst = '',@MaxNo = 0
--取得当前日期字符串
Select @CurrentDate = Cast(year(@InDate) as varchar) + --年
right('00' + cast(month(@InDate) as varchar),2) + -- 月
right('00' + cast(day(@Indate) as varchar),2) --取得数据库中当月的最大一票
Select @MaxNo = max(cast(right(fCRNo,4) as int)) From TOA_CarRepair
Where year(@InDate) = cast(left(fCRNo,4) as int) and
month(@InDate) = Cast(substring(fCRNo,5,2) as int) and
day(@InDate) = cast(substring(fCRNo,7,2) as int)
if (isnull(@MaxNo,0) >0)
Begin
select @Rst = @CurrentDate + right('000' + cast(@MaxNo + 1 as varchar),4)
End
else
Select @Rst = @CurrentDate + '0001'
RETURN @Rst
END
GO
Returns varchar(6)
As
-------------------------------------------------------------------------------------------------
----*Programer*: Zoe
----*Date*: 2009/05/11
-----Purpose: 输入当前的流水号和长度(有四位,五位,六位),按照10进制返回下一个流水号,长度与原来的一样.
-------------------------------------------------------------------------------------------------
Begin
Declare @iNewSeqNo int
Declare @CurrentSeqNo_Int int
Declare @NewSeqNo_Str varchar(6)
Declare @iNewSeqNo_Str varchar(6)
-------------------------------------------------------------------------------------------------
set @CurrentSeqNo_Int=Convert(int,@CurrentSeqNo) ----将传进来的字符转换成整型
set @iNewSeqNo=@CurrentSeqNo_Int+1 ----获得下一个流水号,只需加一(十进制)
set @iNewSeqNo_Str=Convert(varchar(6),@iNewSeqNo) ----将下一个流水号转换成字符型
-------------------------------------------------------------------------------------------------
----将@iNewSeqNo_Str初始化为6位的字符串
If len(@iNewSeqNo_Str)=1 set @iNewSeqNo_Str='00000'+@iNewSeqNo_Str
else If len(@iNewSeqNo_Str)=2 set @iNewSeqNo_Str='0000'+@iNewSeqNo_Str
else If len(@iNewSeqNo_Str)=3 set @iNewSeqNo_Str='000'+@iNewSeqNo_Str
else If len(@iNewSeqNo_Str)=4 set @iNewSeqNo_Str='00'+@iNewSeqNo_Str
else If len(@iNewSeqNo_Str)=5 set @iNewSeqNo_Str='0'+@iNewSeqNo_Str
-------------------------------------------------------------------------------------------------
----按照要求传出流水号,有的流水号是四位,有的是五位。
set @NewSeqNo_Str=right(@iNewSeqNo_Str,@SN_SeqLen)
Return(@NewSeqNo_Str)
-------------------------------------------------------------------------------------------------
End
------------------------------------------------------------------------------------------------- --select [dbo].[GetNextSeqNo_DEC]('9999','4')
--select [dbo].[GetNextSeqNo_DEC]('09999','5')
--select [dbo].[GetNextSeqNo_DEC]('009999','6')