SQL生成流水号

经过了几次的测试终于成功了

declare @Year Int,
        @Month int,
        @Day int,
        @Temp_No varchar(12),
        @NeedNo varchar(4),
    @No varchar(20),
    @Number varchar(50),
    @RandNo varchar(50),
    @Nu varchar(10)
  select @year=Year(GetDate())
  select @Month=Month(GetDate())
  select @Day=Day(GetDate())
  select @No=Str(@Year,4,0)+
                (select
                 case
                  when @Month>=10 then Str(@Month,2,0)
                  when @Month<10 Then '0'+Str(@Month,1,0)
                end)+
                (select
                 case
                  when @Day>=10 then Str(@Day,2,0)
                  when @Day<10 Then '0'+Str(@Day,1,0)
                 end)
select @Number =Idd from lsh
select @Nu=count(*) from lsh
if @Nu ='0' 
BEGIN
select @No=@No+'0001'
END
else
BEGIN
     SELECT @RandNo = STR(CONVERT(int,(SUBSTRING(@Number ,9,4))+1),4,0)
     SELECT @RandNo = REPLACE( @RandNo,' ','0')
     SELECT @No = @No + @RandNo
END
insert into lsh(Idd) values(@No)
select * from lsh