writing sequential numbers with a zero prefix

 

At times you may need a zero as a prefix to sequential numbers

ie:

00001
00002..

00009
00010…

Here is test code that flows through one to ten thousand:

 

declare @number int,
  @prefix varchar(20),
  @display_number varchar(20)
-----------------------------
set @number=0 
while @number<10000
  begin
    set @number=coalesce(@number, 0)
    -----------------------------
    if len(convert(varchar(20), @number))>0
      and @number<>0 
      begin
    -- ones place
        if len(convert(varchar(20), @number))=1
        begin 
          set @display_number='0000'+convert(varchar(20), @number)
        print @display_number
        end
    -- tens place
        if len(convert(varchar(20), @number))=2 
        begin
          set @display_number='000'+convert(varchar(20), @number)
        print @display_number
        end
    -- hundreds place
        if len(convert(varchar(20), @number))=3 
        begin
          set @display_number='00'+convert(varchar(20), @number)
        print @display_number
        end
    -- thousands place
        if len(convert(varchar(20), @number))=4 
        begin
          set @display_number='0'+convert(varchar(20), @number)
        print @display_number
        end
      end
    set @number=@number+1  
  end