--
根据日期自动生成编号
-- 格式:20070517000001
create procedure sp_BuildNumber(
@length int , -- 日期后面的编号长度
@fieldname nvarchar ( 20 ), -- 字段名称
@tablename nvarchar ( 20 ), -- 表名称
@return nvarchar ( 20 ) output -- 输出变量
)
as
begin
declare @sql nvarchar ( 2000 )
set @sql = '
declare @nowdate nvarchar(10)
declare @mincoding nvarchar(20)
declare @coding nvarchar(20)
set @nowdate=convert(nvarchar(10),getdate(),120)
set @nowdate=replace(@nowdate, '' - '' , '''' )
set @mincoding=@nowdate+replicate( '' 0 '' ,@length-1)+ '' 0 ''
select @coding=isnull(max( ' + @fieldname + ' ),@mincoding) from ' + @tablename + ' where ' + @fieldname + ' like @nowdate+ '' % ''
set @return=convert(nvarchar,convert(numeric(18,0),@coding)+1)
'
exec sp_executesql @sql ,N ' @length int,@return nvarchar(20) output ' , @length , @return output
end
GO
-- 格式:20070517000001
create procedure sp_BuildNumber(
@length int , -- 日期后面的编号长度
@fieldname nvarchar ( 20 ), -- 字段名称
@tablename nvarchar ( 20 ), -- 表名称
@return nvarchar ( 20 ) output -- 输出变量
)
as
begin
declare @sql nvarchar ( 2000 )
set @sql = '
declare @nowdate nvarchar(10)
declare @mincoding nvarchar(20)
declare @coding nvarchar(20)
set @nowdate=convert(nvarchar(10),getdate(),120)
set @nowdate=replace(@nowdate, '' - '' , '''' )
set @mincoding=@nowdate+replicate( '' 0 '' ,@length-1)+ '' 0 ''
select @coding=isnull(max( ' + @fieldname + ' ),@mincoding) from ' + @tablename + ' where ' + @fieldname + ' like @nowdate+ '' % ''
set @return=convert(nvarchar,convert(numeric(18,0),@coding)+1)
'
exec sp_executesql @sql ,N ' @length int,@return nvarchar(20) output ' , @length , @return output
end
GO
调用例子(订单编号):
declare
@number
nvarchar
(
20
)
exec sp_BuildNumber 6 , ' OrderNumber ' , ' Orders ' , @number output
exec sp_BuildNumber 6 , ' OrderNumber ' , ' Orders ' , @number output
可根据需要更改输出格式。