So long as n is strictly less than 100, you can use

RIGHT(100+n,2)

In SQL Server 2012, you can also use FORMAT, and the following will work and also give you '100' for 100.

FORMAT(n,'00','en-US')

Yet another approach (not sure how you want to handle numbers greater than 99):

DECLARE @numbers AS TABLE(Val INT)
INSERT INTO @numbers VALUES(1)
INSERT INTO @numbers VALUES(2)
INSERT INTO @numbers VALUES(10)
INSERT INTO @numbers VALUES(11)
INSERT INTO @numbers VALUES(98)
INSERT INTO @numbers VALUES(99)
INSERT INTO @numbers VALUES(100)
SELECT REPLACE(STR(Val, 2), ' ', '0') FROM @numbers

select RIGHT('00'+ convert(varchar, @val), 3)

%2d needs to translated to 2 digit format in sql server

Try this

Declare @val int
set @val=1
SELECT RIGHT('0' + CAST(@val AS VARCHAR(2)), 2)

Result :-

01

In order to check for numbers between 0 to 100 use case statement

SELECT  case when @val>0 and @val<100 then 
               RIGHT('0' + CAST(@val AS VARCHAR), 2)
               else 
               NULL
               END

you can use LPAD function of sql

Try this

SELECT LPAD(n, 2, 0);

Result
01
02
....
10

Explanation : The LPAD function Left-pad the string with 3rd parameter( i.e 0 in given example) up to length given in 2nd parameter ( i.e 2 in given example)

more example

SELECT LPAD('hello', 10, '*');
Result :  *****hello

Try this

SELECT FORMAT(ColumnName,'0000') FROM TableName

Eg:

SELECT FORMAT(50,'0000')

Output: 0050

Do this:

DECLARE @table AS TABLE(n INT)
INSERT INTO @table 
VALUES(1),(3),(5),(9),(10),(50),(99),(100)

Select Case When n<10 Then '0'+Cast(n as varchar(10)) Else Cast(n as varchar(10)) End From @table

SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS YY FROM tableA

This would smart way to solve your problem...

SELECT FORMAT(2, '0#')       .