SQL SERVERÖÐ ÀûÓ÷½·¨Éú³ÉÁ÷Ë®ºÅ

/*
  ¹¦ÄÜ£ºÀûÓú¯Êý´´½¨Á÷Ë®ºÅÈ磺
        fx201005260001,
        fx201005260002,
        fx201005270001
  ×÷Õߣº³ÂÓÀ½¨
  ´´½¨Ê±¼ä£º2010-05-26
 
*/
use master
go
if(db_id('NumberDB')is not null)
drop database NumberDB
go
create database NumberDB
GO
USE NumberDB
go
if(object_id('number')is not null)
drop table number
go
create table number
(
 id varchar(50) not null,
 CreateDate datetime not null
)
go
select * from number
--´´½¨º¯ÊýÉú³ÉÁ÷Ë®ºÅ
if(object_id('fun_CreateNum')is not null)
drop function fun_CreateNum
go
--@profix ǰ׺
--@dateFormat ÈÕÆÚ¸ñʽÈç20050512 £º»ñÈ¡µ±ÌìµÄÈÕÆÚ
--@length ³¤¶È£º¸ù¾ÝÄãµÄʵ¼ÊÐèÇóÀ´ÌîдÄãµÄ³¤¶È£¨½»Ò×Á¿ÉÙÔò³¤¶È¶ÌЩ£©
create function fun_CreateNum(@profix varchar(10),@dateFormat varchar(20),@length int)
returns varchar(100)
as
begin
  declare @MyNo varchar(100)
  select @MyNo= max(id) from number where datediff (dd,CreateDate,getdate())=0 --µÃµ½µ±Ìì×î´óÁ÷Ë®ºÅ
  --isnull(Ä¿±ê²ÎÊý,ÉèÖõÄÖµ)£ºÈç¹ûÄ¿±ê²ÎÊýΪ¿ÕÔò±äΪÉ趨ֵ
  set @MyNo=convert(int,isnull(right(@MyNo,@length),0))+1 --Èç¹û½ñÌìÓмǼ£¬ÔòÔÚ×î´óµÄÁ÷Ë®ºÅÉϼÓ1
  --replicate(É趨ֵ,Öظ´´ÎÊý)£º ½«É趨ֵÖظ´
  set @MyNo=right(replicate(0,@length)+@MyNo,@length)--½«@MyNoÇ°¼Ó0ºó½ØÈ¡ÏàÓ¦µÄλÊý
  set @MyNo=@profix+@dateFormat+@MyNo--Æ´½Ó³ÉÁ÷Ë®ºÅ
  return @MyNo
end
go
--µ÷Óú¯Êý
--convert(varchar(20),getdate(),112):112½«ÈÕÆÚ¸ñʽÉèÖÃΪÈ磺20050512¸ñʽ
select dbo.fun_CreateNum('fx',convert(varchar(20),getdate(),112),5)
--½«Á÷Ë®ºÅ²åÈëÊý¾Ý±íÖÐ
insert into number values(dbo.fun_CreateNum('fx',convert(varchar(20),getdate(),112),5),getdate())
go
select * from number