sql server 按照日期自动生成单据编号的函数,格式为##08080001,##表示打头的单据字符,然后是年月和流水编号。
传入的参数为单据的打头字符和生成单据的日期
一般的调用格式为dbo.GetCostBillID('HP',getdate())
--
按单号和年月获取单据的编号
CREATE
FUNCTION
GetCostBillID(
@headStr
nvarchar
(
10
),
@date
datetime
)
RETURNS
nvarchar
(
50
)
BEGIN
declare
@oid2
nvarchar
(
50
)
declare
@oid
nvarchar
(
50
)
declare
@month
nvarchar
(
2
)
declare
@year
nvarchar
(
2
)
declare
@ym
nvarchar
(
4
)
set
@month
=
month
(
@date
)
if
len
(
@month
)
=
1
set
@month
=
'
0
'
+
@month
--
使月为两位长
set
@year
=right
(
convert
(
nvarchar
,
year
(
@date
)),
2
)
set
@ym
=
@year
+
@month
--
组成年月字符
--
格式CB0808001
if
exists
(
select
*
from
CostBill)
begin
select
top
1
@oid2
=
CostBillID
from
CostBill
order
by
id
desc
--
获取最后一条的单据编号,一定要有id,并且自动生成的,倒排序
end
else
begin
set
@oid2
=
@headStr
+
@ym
+
'
0000
'
--
没有记录是默认为今天
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
订单不是本月的,重新开始一个新的订单流水号
if
convert
(
nvarchar
,
left
(
@oid2
,
6
))
<>
@headStr
+
@ym
begin
--
用本月的年月号开始
set
@oid2
=
@headStr
+
@ym
+
'
0000
'
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
CostBill
where
CostBillID
=
@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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set
@oid
=
convert
(
nvarchar
,
@oid2
)
--
print 'HP'+convert(nvarchar,year(getdate()))+convert(nvarchar,month(getdate()))+@str
RETURN
@oid
END
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)