November 6, 2009 at 3:30 pm
There have been several posts where COALESCE vs ISNULL was being argued.
In order for me to better understand what COALESCE is I looked up the word in BOL.
In the "Remarks"
COALESCE(expression1,...n) is equivalent to this CASE function:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
I was wondering what would happen if MORE than one expression evaluated to Not Null in COALESCE
and then what would happen if MORE than on expression in the CASE evaluated to true.
In COALESCE, The first expression that is not null and the value returned is the same datatype as the expression.
It was my understanding that CASE returned the first expression that was true.
Well it does return the first expression that evaluates to true but....
Result Types
Returns the highest precedence type from the set of types in result_expressions and the optional
else_result_expression. For more information, see Data Type Precedence.
So I attempted to find out what was returned by the Case statement:
Declare
@expression1 as smallmoney
,@expression2 as decimal(18,5)
,@expressionN as float
,@WhatValue as decimal(18,7)
Select @WhatValue = 9.1234567
set @expression1 = @WhatValue
set @expression2 = @WhatValue
set @expressionN = @WhatValue
Select @expression1,@expression2,@expressionN
select CASE
WHEN (@WhatValue IS NOT NULL) THEN @expression1
WHEN (@WhatValue IS NOT NULL) THEN @expression2
WHEN (@WhatValue IS NOT NULL) THEN @expressionN
ELSE NULL
end
In this sample the value returned is @expression1 (the first true) formatted as a 'FLOAT'.
@expression1 = 9.1235 and the value returned is: 9.1234999999999999
Interesting!!
My returned data type of FLOAT is higher than the smallmoney data type @expression1 was evaluated to.
This is the precedence order for the Microsoft® SQL Server™ 2000 data types:
sql_variant (highest)
datetime
smalldatetime
float
real
decimal
money
smallmoney
.etc
My returned data type of FLOAT is higher than the smallmoney data type @expression1 was evaluated to.
Maybe there is a lesson to be learned here.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
November 6, 2009 at 9:39 pm
Hi,
Nice analysis,
Can you post this in to Contribution Center (Question of the Day), so many may learn this section.
November 9, 2009 at 10:21 am
Thanks for your suggestion. I have submitted the article.
Maybe Steve will modify it for a question of the day.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
November 12, 2009 at 5:42 pm
Good article, thanks for sharing!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply