declare @aa int select @aa=3001 select right(cast(100000+@aa as varchar),5)
如果你的编码位数不只5位,只需将1后的零加多就行了. 比如:3001变为0003001 declare @aa int select @aa=3001 select right(cast(1000000+@aa as varchar),5)
3001處理為:003001:select right(replicate(0,6) + cast(3001 as char(4)),6)
SELECT REPLICATE('0', 6 - len(cast(3001 as char(4))))+cast(3001 as char(4))
一个完整的过程,你可以改成相应的函数CREATE PROCEDURE db_get_seq (@current_name nvarchar(20),@seq_len int, @current_value nvarchar(100) output,@errcode int output) AS BEGIN /*@current_name为指定的序号类型. @seq_len为序号转换成字符串时的长度, 若长度短于序号值则截取指定的长度, 若长度长于序号值则从左端添加相应个数的0。 @seq_len范围为:1-20。 @errcode=0为功能正常返回;-1为功能异常返回,返回值不可用。 */ declare @temp_value int declare @sql_str nvarchar(500) declare @i int declare @zero_count int declare @zero_str nchar(19) declare @temp_str nvarchar(100) set @zero_str='0000000000000000000' set @errcode=-1 if (@seq_len>=1 and @seq_len<=20) if exists(SELECT seq_value FROM db_seq_info WHERE seq_name = @current_name) begin BEGIN TRANSACTION SELECT @temp_value= seq_value FROM db_seq_info WHERE seq_name = @current_name --select @sql_str='select @current_value=convert(nvarchar('+convert(varchar(10),@seq_len)+'),' + Convert(nVarChar,@temp_value) + ')' --exec sp_executesql @sql_str if ( @seq_len>len(convert(nvarchar,@temp_value)) ) begin set @current_value=substring(convert(nchar(100),@temp_value),1,@seq_len) end else begin set @current_value=right(convert(nvarchar,@temp_value),@seq_len) end set @zero_count=len(@current_value+'1')-1-len(rtrim(@current_value)) set @temp_str=substring(@zero_str,1,@zero_count) set @current_value=@temp_str+rtrim(@current_value) print @current_value set @temp_value=@temp_value+1 UPDATE db_seq_info SET seq_value = @temp_value WHERE seq_name = @current_name if @@error = 0 begin set @errcode=0 commit transaction return 0 end else begin rollback transaction return -1 end end END 注: CREATE TABLE [dbo].[db_seq_info] ( [seq_name] [nvarchar] (20) NOT NULL , [seq_value] [int] NOT NULL , CONSTRAINT [PK_seq_info] PRIMARY KEY CLUSTERED )
select @aa=3001
select right(cast(100000+@aa as varchar),5)
比如:3001变为0003001
declare @aa int
select @aa=3001
select right(cast(1000000+@aa as varchar),5)
AS
BEGIN
/*@current_name为指定的序号类型.
@seq_len为序号转换成字符串时的长度,
若长度短于序号值则截取指定的长度,
若长度长于序号值则从左端添加相应个数的0。
@seq_len范围为:1-20。
@errcode=0为功能正常返回;-1为功能异常返回,返回值不可用。
*/
declare @temp_value int
declare @sql_str nvarchar(500)
declare @i int
declare @zero_count int
declare @zero_str nchar(19)
declare @temp_str nvarchar(100)
set @zero_str='0000000000000000000'
set @errcode=-1
if (@seq_len>=1 and @seq_len<=20)
if exists(SELECT seq_value FROM db_seq_info WHERE seq_name = @current_name)
begin
BEGIN TRANSACTION
SELECT @temp_value= seq_value FROM db_seq_info WHERE seq_name = @current_name
--select @sql_str='select @current_value=convert(nvarchar('+convert(varchar(10),@seq_len)+'),' + Convert(nVarChar,@temp_value) + ')'
--exec sp_executesql @sql_str if ( @seq_len>len(convert(nvarchar,@temp_value)) )
begin
set @current_value=substring(convert(nchar(100),@temp_value),1,@seq_len)
end
else
begin
set @current_value=right(convert(nvarchar,@temp_value),@seq_len)
end
set @zero_count=len(@current_value+'1')-1-len(rtrim(@current_value))
set @temp_str=substring(@zero_str,1,@zero_count)
set @current_value=@temp_str+rtrim(@current_value)
print @current_value
set @temp_value=@temp_value+1
UPDATE db_seq_info SET seq_value = @temp_value WHERE seq_name = @current_name if @@error = 0
begin
set @errcode=0
commit transaction
return 0
end
else
begin
rollback transaction
return -1
end
end
END
注:
CREATE TABLE [dbo].[db_seq_info] (
[seq_name] [nvarchar] (20) NOT NULL ,
[seq_value] [int] NOT NULL ,
CONSTRAINT [PK_seq_info] PRIMARY KEY CLUSTERED
)