日期+流水=编号 的实现方法

create table tb(ID nvarchar(20),col nvarchar(10))
go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create function f_getID()
returns nchar(11)
as
begin
  declare @ID int
  declare @Nowdate nvarchar(8),@OtherDate nvarchar(8),@Num nchar(13)
  select @Nowdate = convert(char(8),getdate(),112),@OtherDate = '19990101',@ID = 0,@Num = ''
  select @OtherDate = substring(ID,2,8),@ID = right(ID,2) from tb where substring(ID,2,8) = @Nowdate
  if @OtherDate <> @Nowdate
  begin
    select @ID = 0
  end;
  select @Num = 'J' + @Nowdate + right((100 + @ID + 1),2)
  return @Num
end

insert into tb
select dbo.f_getID(),'aa'

select * from tb