T-SQL calculation with a CASE statement isn't working [closed]

Question

I'm scratching my head over this. I'm sure it's something simple, but I'm stumped. I have the following T-SQL, which I'm running in SQL Server 2012:

DECLARE @MAX_naam AS varchar(MAX) = (SELECT SUBSTRING(MAX(naam),LEN(MAX(naam))-1,LEN(MAX(naam))) FROM incident); -- = 22
DECLARE @maxnaam_length AS int = LEN(@MAX_naam);
DECLARE @num_zeroes AS int = 5 - @maxnaam_length;
DECLARE @zeroes AS varchar = 
    CASE WHEN @num_zeroes = 0 THEN ''
    WHEN @num_zeroes = 1 THEN '0'
    WHEN @num_zeroes = 2 THEN '00'
    WHEN @num_zeroes = 3 THEN '000'
    ELSE '' END;
select @zeroes

@MAX_naam is currently 22. @maxnaam_length is, correctly 2, and @num_zeroes is 3. I've verified each of these. However, despite expecting @zeroes to be '000', it comes out as '0'. Why?

Answer 1

DECLARE @zeroes AS varchar = ...

Should be

DECLARE @zeroes varchar(11) = ...
---- important --------^^^^

For more info see Bad habits to kick : declaring VARCHAR without (length).

Answer 2

Try specifying a size for your @zeroes variable

DECLARE @zeroes AS varchar(64) = ...

In my case this resulted in the string 000.