My calculation with a CASE statement isn\'t working

Question

Post reply

My calculation with a CASE statement isn\'t working

Melanie Peterson

SSCrazy

Points: 2387

April 26, 2022 at 8:20 pm

#4022673

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); -- = 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?

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

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

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.

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 2

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

ratbak Ten Centuries Points: 1358 More actions · Answer 3

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

ScottPletcher SSC Guru Points: 101092 More actions · Answer 4

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); -- = 22
DECLARE @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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 5

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 6

Oh bless you, Scott Pletcher, this works wonderfully.

This reply was modified 2 years, 6 months ago by Melanie Peterson.
This reply was modified 2 years, 6 months ago by Melanie Peterson.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 7

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 8

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;

DesNorton SSC-Insane Points: 24382 More actions · Answer 9

This should give you the same result as your complicated calculation

SELECT @naam = 'I' + CONVERT(char(4), GETDATE(), 12) + '-' + RIGHT('00000' + @MAX_naam, 5)

How to post data/code on a forum to get the best help.

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 10

DesNorton, your solution is so much simpler. Thank you very much!