Try this Quick T-SQL Quiz!

Question

Post reply

Try this Quick T-SQL Quiz!

John Jakob

SSCommitted

Points: 1720
More actions
June 11, 2004 at 1:10 pm

#111559

What value of 'Result' will be printed below? (It wasn't what I expected...)
DECLARE @F FLOAT
SELECT @F = 1234.5678
SELECT CASE
          WHEN @F < 1.0 THEN CONVERT(DECIMAL(10, 4), @F)
          ELSE               CONVERT(DECIMAL(10, 0), @F)
       END AS 'Result'

Viewing 6 posts - 1 through 5 (of 5 total)

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

John Jakob SSCommitted Points: 1720 More actions · Answer 1

-- This is OK (What I Expect) -- Result: 1234.5678

DECLARE @F1 FLOAT

SELECT @F1 = 1234.5678

SELECT CONVERT(DECIMAL(10, 4), @F1)

-- This is OK (What I Expect) -- Result: 1235

DECLARE @F2 FLOAT

SELECT @F2 = 1234.5678

SELECT CONVERT(DECIMAL(10, 0), @F2)

-- Huh? (I Don't Understand This At All) -- Result: 1235.0000

DECLARE @F3 FLOAT

SELECT @F3 = 1234.5678

SELECT CASE

WHEN @F3 < 1.0 THEN CONVERT(DECIMAL(10, 4), @F3)

ELSE CONVERT(DECIMAL(10, 0), @F3)

END AS 'Result'

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

@F3 was greater than one so the ELSE prevails giving you the exact same answer as you had in your second example.

--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)

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 3

From BOL:

The return type from a CASE statement is:

"Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression"

So You get DECIMAL(10, 4) and not DECIMAL(10, 0) since it has higher precedence

/rockmoose

You must unlearn what You have learnt

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Dummy me... missed the formatting thing... thought he didn't understand the "ELSE". Nice job, Rockmoose!

--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)

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 5

Yeah, I assumed it just wasn't the ELSE thing.

Too simple, it must have been something else that upset John Jakob !

cheers,

/rockmoose

You must unlearn what You have learnt