1
---流动人员号自动编号函数,由区域代码-年月日-序列号组成的 每日新序号
2---geovindu@163.com 涂聚文 www.dusystem.com
3if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetDayFloatingID]') and xtype in (N'FN', N'IF', N'TF'))
4drop function [dbo].[GetDayFloatingID]
5GO
6CREATE FUNCTION GetDayFloatingID(@headStr nvarchar(10),@date datetime)
7RETURNS nvarchar(50)
8BEGIN
9declare @oid2 nvarchar(50)
10declare @oid nvarchar(50)
11declare @day nvarchar(2)
12declare @month nvarchar(2)
13declare @year nvarchar(4)
14declare @ym nvarchar(8)
15set @day=day(@date)
16if len(@day)=1
17 set @day='0'+@day --使日为两位长
18set @month=month(@date)
19if len(@month)=1
20 set @month='0'+@month --使月为两位长
21--set @year=right(convert(nvarchar,year(@date)),2)
22set @year=convert(nvarchar,year(@date))
23set @ym=@year+@month+@day --组成年月日字符
24
25--格式BJ200808200001
26if exists(select * from Populations)
27begin
28 select top 1 @oid2=FloatingID from Populations order by FloatingID desc --获取最后一条的编号，一定要有id，并且自动生成的，倒排序
29end
30else
31begin
32 set @oid2=@headStr+@ym+'00000' --没有记录是默认为今天
33end
34
35--流水号不是本月的，重新开始一个新的流水号
36if convert(nvarchar,left(@oid2,6))<>@headStr+@ym
37begin
38--用本月的年月号开始
39 set @oid2=@headStr+@ym+'00000'
40end
41
42declare @str nvarchar(50) --临时流水号
43
44set @str=convert(nvarchar,(convert(int,right(@oid2,4))+1)) --流水号加一
45while (5-len(@str)>0)
46begin
47 set @str='0'+@str
48end
49set @oid2=@headStr+@ym+@str
50--print @oid2
51
52--如果该流水号已经存在，则重新获取
53while exists(select * from Populations where FloatingID=@oid2)
54begin
55
56 set @str=convert(nvarchar,(convert(int,right(@oid2,5))+1)) --流水号加一
57 while (5-len(@str)>0)
58 begin
59 set @str='0'+@str
60 end
61 set @oid2=@headStr+@ym+@str
62-- print @oid2
63end
64
65set @oid=convert(nvarchar,@oid2)
66--print 'UL'+convert(nvarchar,year(getdate()))+convert(nvarchar,month(getdate()))+@str
67RETURN @oid
68END
69GO
70--测试
71DECLARE @S varchar(30)
72select @S=dbo.GetDayFloatingID('02',getdate())
73select @s as '流动编号'
74
75--按月自动增长
76--如果当月，没有记录号，开始创建，如果有，在此基此上加1,以月新增长序列号
77if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMonthFloatingID]') and xtype in (N'FN', N'IF', N'TF'))
78drop function [dbo].[GetMonthFloatingID]
79GO
80CREATE FUNCTION GetMonthFloatingID(@headStr nvarchar(10),@idate datetime)
81RETURNS nvarchar(50)
82BEGIN
83--@idate datetime,@headStr varchar(10),
84declare @fid varchar(100),@dat varchar(20),@ym varchar(20),@str nvarchar(50),@olde varchar(30),@y varchar(10),@m varchar(10),@d varchar(10)
85--set @headStr='01'
86--set @idate=cast('2009-04-3' as datetime) --getdate()
87--set @ym=cast(getdate() as varchar(30))
88--找到当月最大的值
89select top 1 @fid=FloatingID from Populations
90 where month(cast(substring(FloatingID,3,8) as datetime))=month(@idate) and year(cast(substring(FloatingID,3,8) as datetime))=year(@idate)
91 order by substring(FloatingID,11,5) desc
92--select @fid=MAX(substring(FloatingID,11,5)) from Populations
93 -- where month(getdate())
94 set @y=cast(year(@idate) as varchar(10)) --
95 set @m=cast(month(@idate) as varchar(10))
96 if len(@m)=1
97 set @m='0'+@m
98 set @d=cast(day(@idate) as varchar(10))
99 if len(@d)=1
100 set @d='0'+@d
101 set @ym=@y+@m+@d
102if @fid<>''
103 --加一
104 begin
105
106 select @str=convert(nvarchar,(convert(int,right(@fid,5))+1))
107 while (5-len(@str)>0)
108 begin
109 set @str='0'+@str
110 end
111 set @olde=@headStr+@ym+@str
112 --select @olde
113 end
114else
115 begin
116 set @olde=@headStr+@ym+'00000'
117 --select @olde
118 --print '2'
119 end
120set @olde=convert(nvarchar,@olde)
121--print 'UL'+convert(nvarchar,year(getdate()))+convert(nvarchar,month(getdate()))+@str
122RETURN @olde
123end
124GO
125--测试
126select dbo.GetMonthFloatingID('09',getdate())
127
128---2009-03-04 涂聚文 geovindu@163.com
---流动人员号自动编号函数,由区域代码-年月日-序列号组成的 每日新序号2
---geovindu@163.com 涂聚文 www.dusystem.com3
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetDayFloatingID]') and xtype in (N'FN', N'IF', N'TF'))4
drop function [dbo].[GetDayFloatingID]5
GO6
CREATE FUNCTION GetDayFloatingID(@headStr nvarchar(10),@date datetime)7
RETURNS nvarchar(50)8
BEGIN9
declare @oid2 nvarchar(50)10
declare @oid nvarchar(50)11
declare @day nvarchar(2)12
declare @month nvarchar(2)13
declare @year nvarchar(4)14
declare @ym nvarchar(8)15
set @day=day(@date)16
if len(@day)=117
set @day='0'+@day --使日为两位长18
set @month=month(@date)19
if len(@month)=120
set @month='0'+@month --使月为两位长21
--set @year=right(convert(nvarchar,year(@date)),2)22
set @year=convert(nvarchar,year(@date))23
set @ym=@year+@month+@day --组成年月日字符24
25
--格式BJ20080820000126
if exists(select * from Populations)27
begin28
select top 1 @oid2=FloatingID from Populations order by FloatingID desc --获取最后一条的编号，一定要有id，并且自动生成的，倒排序29
end30
else31
begin32
set @oid2=@headStr+@ym+'00000' --没有记录是默认为今天33
end34
35
--流水号不是本月的，重新开始一个新的流水号36
if convert(nvarchar,left(@oid2,6))<>@headStr+@ym37
begin38
--用本月的年月号开始39
set @oid2=@headStr+@ym+'00000'40
end41
42
declare @str nvarchar(50) --临时流水号43
44
set @str=convert(nvarchar,(convert(int,right(@oid2,4))+1)) --流水号加一45
while (5-len(@str)>0)46
begin47
set @str='0'+@str 48
end49
set @oid2=@headStr+@ym+@str50
--print @oid251
52
--如果该流水号已经存在，则重新获取53
while exists(select * from Populations where FloatingID=@oid2)54
begin55
56
set @str=convert(nvarchar,(convert(int,right(@oid2,5))+1)) --流水号加一57
while (5-len(@str)>0)58
begin59
set @str='0'+@str 60
end61
set @oid2=@headStr+@ym+@str62
-- print @oid263
end64
65
set @oid=convert(nvarchar,@oid2)66
--print 'UL'+convert(nvarchar,year(getdate()))+convert(nvarchar,month(getdate()))+@str67
RETURN @oid68
END69
GO70
--测试71
DECLARE @S varchar(30)72
select @S=dbo.GetDayFloatingID('02',getdate())73
select @s as '流动编号'74
75
--按月自动增长76
--如果当月，没有记录号，开始创建，如果有，在此基此上加1,以月新增长序列号77
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMonthFloatingID]') and xtype in (N'FN', N'IF', N'TF'))78
drop function [dbo].[GetMonthFloatingID]79
GO80
CREATE FUNCTION GetMonthFloatingID(@headStr nvarchar(10),@idate datetime)81
RETURNS nvarchar(50)82
BEGIN83
--@idate datetime,@headStr varchar(10),84
declare @fid varchar(100),@dat varchar(20),@ym varchar(20),@str nvarchar(50),@olde varchar(30),@y varchar(10),@m varchar(10),@d varchar(10)85
--set @headStr='01'86
--set @idate=cast('2009-04-3' as datetime) --getdate()87
--set @ym=cast(getdate() as varchar(30))88
--找到当月最大的值89
select top 1 @fid=FloatingID from Populations 90
where month(cast(substring(FloatingID,3,8) as datetime))=month(@idate) and year(cast(substring(FloatingID,3,8) as datetime))=year(@idate)91
order by substring(FloatingID,11,5) desc92
--select @fid=MAX(substring(FloatingID,11,5)) from Populations 93
-- where month(getdate())94
set @y=cast(year(@idate) as varchar(10)) --95
set @m=cast(month(@idate) as varchar(10))96
if len(@m)=197
set @m='0'+@m98
set @d=cast(day(@idate) as varchar(10))99
if len(@d)=1100
set @d='0'+@d101
set @ym=@y+@m+@d102
if @fid<>''103
--加一104
begin105
106
select @str=convert(nvarchar,(convert(int,right(@fid,5))+1))107
while (5-len(@str)>0)108
begin109
set @str='0'+@str 110
end111
set @olde=@headStr+@ym+@str112
--select @olde113
end114
else115
begin116
set @olde=@headStr+@ym+'00000'117
--select @olde118
--print '2'119
end 120
set @olde=convert(nvarchar,@olde)121
--print 'UL'+convert(nvarchar,year(getdate()))+convert(nvarchar,month(getdate()))+@str122
RETURN @olde123
end124
GO125
--测试126
select dbo.GetMonthFloatingID('09',getdate())127
128
---2009-03-04 涂聚文 geovindu@163.com