企业级编号
-------------------------------------------
--------------------------------------------------
--drop table MyReocrdNo
use pubs
go
--drop table MyReocrdNo
create table MyReocrdNo(
mrn_tablename varchar(20) not null primary key,
mrn_sign varchar(2) null,
mrn_lastno int not null,
mrn_lastdate datetime not null,
nomax int null
)
go
--------------------------------------------------
--drop PROCEDURE getBillNo
go
--alter PROCEDURE getBillNo @tablename varchar(20), @billno varchar(20) output ,@sign varchar(2) = ''
create PROCEDURE getBillNo @tablename varchar(20), @billno varchar(20) output ,@sign varchar(2) = ''
AS
--declare
-- @tablename varchar(20), -表名
-- @billno varchar(20) -生成的编号
declare @lastdate datetime,
@nowdate datetime,
@tmpInt int,
@tmpStr varchar(4),
@tmpsign varchar(2)
--set @tablename='poa'
select @nowdate=getdate()--获得当前时间
if not exists(select mrn_tablename from MyReocrdNo where mrn_tablename=@tablename)
begin
insert into MyReocrdNo(mrn_tablename,mrn_sign,mrn_lastno,mrn_lastdate,nomax) values(@tablename,@sign,1,@nowdate,1)
set @tmpInt = 1
end
else
begin
select @lastdate=mrn_lastdate from MyReocrdNo where mrn_tablename=@tablename
if (datediff(day, @nowdate , @lastdate ) = 0 )
begin
update MyReocrdNo set mrn_lastno = mrn_lastno + 1,nomax=nomax+1 where mrn_tablename=@tablename
select @tmpInt=mrn_lastno from MyReocrdNo where mrn_tablename=@tablename
end
else if (datediff(day, @nowdate , @lastdate ) < 0 )
begin
update MyReocrdNo set mrn_lastno = 1 ,mrn_lastdate = @nowdate,nomax=nomax+1 where mrn_tablename=@tablename
set @tmpInt=1
end
else if (datediff(day, @nowdate , @lastdate ) > 0 )
begin
update MyReocrdNo set nomax=nomax+1 where mrn_tablename=@tablename
select @tmpInt=nomax from MyReocrdNo where mrn_tablename=@tablename
end
end
---------------------------------------------------------------------------
-- select @tmpInt
-- select len(rtrim(ltrim(str(1))))
select @tmpStr=
case len(rtrim(ltrim(str(@tmpInt))))
when 1 then '000'+ltrim(str(@tmpInt))
when 2 then '00'+ltrim(str(@tmpInt))
when 3 then '0'+ltrim(str(@tmpInt))
when 4 then ltrim(str(@tmpInt))
end
set @billno =+ right(convert(varchar(10) ,@nowdate,112),6) + @tmpStr
select @tmpsign= mrn_sign from MyReocrdNo where mrn_tablename=@tablename
set @billno=ltrim(rtrim(@tmpsign)) + @billno
--select @billno
GO
---------------------------------------------------------------------------
--CREATE PROCEDURE getBillNo @tablename varchar(20), @billno varchar(20) output
declare
@mybillno varchar(20)
exec getBillNo 'pod', @mybillno output,'PO'
select @mybillno
--select * from MyReocrdNo