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

  1 -- -流动人员号自动编号函数,由区域代码-年月日-序列号组成的 每日新序号
  2 -- -geovindu@163.com 涂聚文 www.dusystem.com
  3 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 GO
  6 CREATE   FUNCTION  GetDayFloatingID( @headStr   nvarchar ( 10 ), @date   datetime )
  7 RETURNS   nvarchar ( 50 )
  8 BEGIN
  9 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 ) = 1
 17      set   @day = ' 0 ' + @day   -- 使日为两位长
 18 set   @month = month ( @date )
 19 if   len ( @month ) = 1
 20      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 -- 格式BJ200808200001
 26 if   exists ( select   *   from  Populations)
 27 begin
 28      select    top   1   @oid2 = FloatingID  from  Populations   order   by  FloatingID  desc   -- 获取最后一条的编号，一定要有id，并且自动生成的，倒排序
 29 end
 30 else
 31 begin
 32      set   @oid2 = @headStr + @ym + ' 00000 '   -- 没有记录是默认为今天
 33 end
 34
 35 -- 流水号不是本月的，重新开始一个新的流水号
 36 if   convert ( nvarchar , left ( @oid2 , 6 )) <> @headStr + @ym
 37 begin
 38 -- 用本月的年月号开始
 39      set   @oid2 = @headStr + @ym + ' 00000 '
 40 end
 41
 42 declare   @str   nvarchar ( 50 )  -- 临时流水号
 43
 44 set   @str = convert ( nvarchar ,( convert ( int , right ( @oid2 , 4 )) + 1 ))  -- 流水号加一
 45 while  ( 5 - len ( @str ) > 0 )
 46 begin
 47       set   @str = ' 0 ' + @str     
 48 end
 49 set   @oid2 = @headStr + @ym + @str
 50 -- print @oid2
 51
 52 -- 如果该流水号已经存在，则重新获取
 53 while   exists ( select   *   from  Populations  where  FloatingID = @oid2 )
 54 begin
 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
 63 end
 64
 65 set   @oid = convert ( nvarchar , @oid2 )
 66 -- print 'UL'+convert(nvarchar,year(getdate()))+convert(nvarchar,month(getdate()))+@str
 67 RETURN   @oid
 68 END
 69 GO
 70 -- 测试
 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 GO
 80 CREATE   FUNCTION  GetMonthFloatingID( @headStr   nvarchar ( 10 ), @idate   datetime )
 81 RETURNS   nvarchar ( 50 )
 82 BEGIN
 83 -- @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 )  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
102 if   @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
114 else
115     begin
116           set   @olde = @headStr + @ym + ' 00000 '
117           -- select @olde
118      -- print '2'
119     end  
120 set   @olde = convert ( nvarchar , @olde )
121 -- print 'UL'+convert(nvarchar,year(getdate()))+convert(nvarchar,month(getdate()))+@str
122 RETURN   @olde
123 end
124 GO
125 -- 测试
126 select  dbo.GetMonthFloatingID( ' 09 ' , getdate ())
127
128 -- -2009-03-04 涂聚文 geovindu@163.com