你的位置: Kiyo'Space首页 数据库 阅读文章 欢迎留下您的足迹

自动为指定表的指定字段生成编号的存储过程

[ 数据库 ] 分享

 

–根据日期自动生成编号
–格式: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

没有评论

  • (Required)
  • (Required, will not be published)