For a two character string you can do this…
declare @number tinyint
set @number = 2
select case when len( @number ) = 1 then ‘0′ else ” end + cast( @number as varchar(2) )
For a number that will be greater than two characters in length you can do this…
declare @number int
declare @string varchar(10)
declare @size_of_fixed_string tinyint
set @size_of_fixed_string = 10
set @number = 40
print replicate( ‘0′, @size_of_fixed_string )
set @string = left( replicate( ‘0′, @size_of_fixed_string ), @size_of_fixed_string – len( @number ) ) + cast( @number as varchar(10) )
print @string
