sql 用户定义函数自动生成自增长ID

  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
                set @d='0'+@d
                 set @ym=@y+@m+@d
if @fid<>''
   --加一
    begin

        select @str=convert(nvarchar,(convert(int,right(@fid,5))+1))
                while (5-len(@str)>0)
        begin
             set @str='0'+@str    
        end
                set @olde=@headStr+@ym+@str
        --select @olde
    end
else
   begin
         set @olde=@headStr+@ym+'00000'
         --select @olde
    --print '2'
   end 
set @olde=convert(nvarchar,@olde)
--print 'UL'+convert(nvarchar,year(getdate()))+convert(nvarchar,month(getdate()))+@str
RETURN @olde
end
GO
--测试
select dbo.GetMonthFloatingID('09',getdate())

---2009-03-04 涂聚文 geovindu@163.com