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)
  &nb