# My calculation with a CASE statement isn\'t working

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

`DECLARE @MAX_naam AS varchar(MAX) = (SELECT SUBSTRING(MAX(naam),LEN(MAX(naam))-1,LEN(MAX(naam))) FROM incident); -- = 22DECLARE @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?

• `DECLARE @zeroes AS varchar`

If you declare a variable or parameter as varchar w/o specifying length -- e.g., varchar(5) -- it is implicitly declared as varchar(1), and can only contain one character.

• OK, you're right. However, when I change my code to DECLARE @zeroes AS varchar(5) I get the same result. @zeroes = '0'

• I don't - I get '000' if @max_naam = '22'. Does @MAX_naam really need to be varchar(max)?

`DECLARE @MAX_naam AS varchar(MAX) = '22'DECLARE @maxnaam_length AS int = LEN(@MAX_naam);DECLARE @num_zeroes AS int = 5 - @maxnaam_length;SELECT @MAX_naam AS MAX_naam,@maxnaam_length AS maxnaam_length, @num_zeroes AS num_zeroes;DECLARE @zeroes AS VARCHAR(5) = 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`

What does this return?

`SELECT MAX(naam) AS max_naam, SUBSTRING(MAX(naam),LEN(MAX(naam))-1,LEN(MAX(naam))) AS max_naam_len FROM incident`

•

Rather than a CASE, use the built-in REPLICATE function:

`DECLARE @MAX_naam AS varchar(MAX) SET @MAX_naam = 22 --(SELECT SUBSTRING(MAX(naam),LEN(MAX(naam))-1,LEN(MAX(naam))) FROM incident); -- = 22DECLARE @maxnaam_length AS int = LEN(@MAX_naam);DECLARE @num_zeroes AS int = 5 - @maxnaam_length;DECLARE @zeroes varchar(5) = REPLICATE('0', @num_zeroes)SELECT @zeroes`

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

• Are you just trying to LPAD zeros for a total length of 5?  If so, this will make your life easier...

`DECLARE @SomeInt INT = 22; SELECT RIGHT(CONCAT('00000',@SomeInt),5);`

Note that @SomeInt in the formula could actually be a column name from a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• Oh bless you, Scott Pletcher, this works wonderfully.

• Melanie Peterson wrote:

Oh bless you, Scott Pletcher, this works wonderfully.

I have a question just out of curiosity... what are you going to do with the zeroes once you have the correct number of zeros returned as a string?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• `DECLARE @naam AS varchar(20) = 'I' + RIGHT(CAST(DATEPART(yy,GETDATE()) AS varchar),2) + RIGHT('0'+CAST(DATEPART(mm,GETDATE()) AS varchar),2) + '-' + @zeroes + @MAX_naam;`
• This should give you the same result as your complicated calculation

`SELECT @naam = 'I' + CONVERT(char(4), GETDATE(), 12) + '-' + RIGHT('00000' + @MAX_naam, 5)`
• DesNorton, your solution is so much simpler. Thank you very much!

Viewing 11 posts - 1 through 10 (of 10 total)