I have the following code in a stored procedure and am trying to conditionally format a calculated number based on its length (if the number is less than 4 digits, pad with leading zeros). However, my case expression is not working. The "formattedNumber2" result is the one I'm looking for.

I'm assuming the case expression treats the variable strangely, but I also don't know of a way around this.

DECLARE @Number int = 5

SELECT
    CASE 
       WHEN (LEN(CONVERT(VARCHAR, @Number)) > 4) 
          THEN @Number
       ELSE RIGHT('0000' + CAST(@Number AS VARCHAR(4)), 4) 
    END AS formattedNumber,
    LEN(CONVERT(VARCHAR, @Number)) AS numberLength,
    RIGHT('0000' + CAST(@Number AS VARCHAR(4)), 4) AS formattedNumber2

I get the following results when I run the query:

formattedNumber   numberLength  formattedNumber2
-------------------------------------------------
         5             1              0005