--按单号和年月获取单据的编号
CREATE FUNCTION GetDJBH(@headStr nvarchar(10),@date datetime)
RETURNS nvarchar(50)
BEGIN
declare @oid2 nvarchar(50)
declare @oid nvarchar(50)
declare @month nvarchar(2)
declare @year nvarchar(4)
declare @ym nvarchar(6)
set @month=month(@date)
if len(@month)=1
set @month='0'+@month --使月为两位长
set @year=right(convert(nvarchar,year(@date)),4)
set @ym=@year+@month --组成年月字符
--格式D201108001
if exists(select * from BUYmain)
begin
select top 1 @oid2=BPD_DJBH from BUYmain order by id desc --获取最后一条的单据编号,一定要有id,并且自动生成的,倒排序
end
else
begin
set @oid2=@headStr+@ym+'0000' --没有记录是默认为今天
end
--订单不是本月的,重新开始一个新的订单流水号
if convert(nvarchar,left(@oid2,7))<>@headStr+@ym
begin
--用本月的年月号开始
set @oid2=@headStr+@ym+'0000'
end
declare @str nvarchar(50) --临时单号
set @str=convert(nvarchar,(convert(int,right(@oid2,4))+1)) --订单号加一
while (4-len(@str)>0)
begin
set @str='0'+@str
end
set @oid2=@headStr+@ym+@str
--print @oid2
--如果该订单号已经存在,则重新获取
while exists(select * from BUYmain where BPD_DJBH=@oid2)
begin
set @str=convert(nvarchar,(convert(int,right(@oid2,4))+1)) --订单号加一
while (4-len(@str)>0)
begin
set @str='0'+@str
end
set @oid2=@headStr+@ym+@str
-- print @oid2
end
set @oid=convert(nvarchar,@oid2)
--print 'D'+convert(nvarchar,year(getdate()))+convert(nvarchar,month(getdate()))+@str
RETURN @oid
END
create table BUYmain
(
id int Identity not null,
BPD_DJBH VARCHAR(50) PRIMARY KEY DEFAULT dbo.GetDJBH('D',getdate()),
BPD_USERCODE varchar(50),
BPD_TYPE int
)
select * from BUYmain
INSERT BUYmain(BPD_USERCODE,BPD_TYPE) VALUES('bj000',1)
INSERT BUYmain(BPD_USERCODE,BPD_TYPE) VALUES('bj000',2)
INSERT BUYmain(BPD_USERCODE,BPD_TYPE) VALUES('bj000',1)
INSERT BUYmain(BPD_USERCODE,BPD_TYPE) VALUES('bj000',2)