自动编号
ALTER PROCEDURE [dbo].[Getbm] @tablename nvarchar(250),@bmzd nvarchar(250),
@bmstr nvarchar(250),
@idstr nvarchar(20)
As
declare @maxnumber nvarchar(50)
declare @tabelname1 nvarchar(50)
declare @bmzd1 nvarchar(50)
declare @bmstr1 nvarchar(50)
declare @sqlstr nvarchar(500)
declare @ParmDefinition nvarchar(200)
declare @idstr1 nvarchar(20)
declare @sparam varchar(100)
declare @TODAY char(6)
select @TODAY=right(convert(char(8),getdate(),112),6)
set @maxnumber = 0
set @tabelname1 = @tablename
set @bmzd1 = @bmzd
set @bmstr1=@bmstr
set @idstr1=@idstr
set @sparam =@bmstr1+@TODAY+ replace(@idstr1, '0', '[0-9]')
set @sqlstr = N'select Top 1 @maxd='+@bmzd1+' from '+@tabelname1+' where '+@bmzd1+' like '''+@sparam+''' order by '+@bmzd1+' desc'
SET @ParmDefinition = N'@maxd nvarchar(250) output'
EXECUTE sp_executesql @sqlstr, @ParmDefinition,@maxd =@maxnumber output
if @maxnumber is null or left(@maxnumber,(6+len(@bmstr)))<>@bmstr+@TODAY
begin
select @maxnumber=@bmstr+@TODAY+@idstr1
end
select @maxnumber=left(@maxnumber,(6+len(@bmstr)))+ right(@idstr1 + ltrim(convert(char(20),convert(int,right(@maxnumber,len(@idstr1) )))+1),len(@idstr1) )
select number=@maxnumber
@bmstr nvarchar(250),
@idstr nvarchar(20)
As
declare @maxnumber nvarchar(50)
declare @tabelname1 nvarchar(50)
declare @bmzd1 nvarchar(50)
declare @bmstr1 nvarchar(50)
declare @sqlstr nvarchar(500)
declare @ParmDefinition nvarchar(200)
declare @idstr1 nvarchar(20)
declare @sparam varchar(100)
declare @TODAY char(6)
select @TODAY=right(convert(char(8),getdate(),112),6)
set @maxnumber = 0
set @tabelname1 = @tablename
set @bmzd1 = @bmzd
set @bmstr1=@bmstr
set @idstr1=@idstr
set @sparam =@bmstr1+@TODAY+ replace(@idstr1, '0', '[0-9]')
set @sqlstr = N'select Top 1 @maxd='+@bmzd1+' from '+@tabelname1+' where '+@bmzd1+' like '''+@sparam+''' order by '+@bmzd1+' desc'
SET @ParmDefinition = N'@maxd nvarchar(250) output'
EXECUTE sp_executesql @sqlstr, @ParmDefinition,@maxd =@maxnumber output
if @maxnumber is null or left(@maxnumber,(6+len(@bmstr)))<>@bmstr+@TODAY
begin
select @maxnumber=@bmstr+@TODAY+@idstr1
end
select @maxnumber=left(@maxnumber,(6+len(@bmstr)))+ right(@idstr1 + ltrim(convert(char(20),convert(int,right(@maxnumber,len(@idstr1) )))+1),len(@idstr1) )
select number=@maxnumber