Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Henrik Svensson
Starting Member
25 Posts |
Posted - 2013-02-13 : 04:09:07
|
Hi!I have a question. I know this doesn't seem like "good SQL programming", but I came across some complex piece of code that was constructed in a similar way as my simplified exemples below, that made an incorrect calculation, and would really appreciate if anyone might explain to me what causes this behaviour, i.e. what is the explanation to why SQL Server gives me different results when running the two queries below?SELECT 100.00 * CASE WHEN 1=1 THEN '0.55000' ELSE 1.0 END SELECT 100.00 * CASE WHEN 1=1 THEN '0.55000' ELSE '1.0' END With best regards,Henrik Svensson |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-02-13 : 04:31:25
|
See the output of this to understandselect '0.55000'+1.0,'0.55000'+1.00MadhivananFailing to plan is Planning to fail |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-13 : 08:27:24
|
To add to what Madhivanan said: run this query and you will see that the data types returned from the case expression are are different.SELECT SQL_VARIANT_PROPERTY( CASE WHEN 1=1 THEN '0.55000' ELSE 1.0 END,'BaseType') SELECT SQL_VARIANT_PROPERTY( CASE WHEN 1=1 THEN '0.55000' ELSE '1.0' END ,'BaseType') You can also look at the precision and scale of the case expression of the numeric type and the maxlength of the varchar type to get a better understanding. All of that comes about because the data types of each when clause in a case expression has to be of the same or it should be possible to make them so by an implicit conversion. In your first example, an implicit conversion is required because the when expression is of varchar type and the else is of decimal type. Whether it gets converted to varchar or decimal is dependent on the conversion precedence, and decimal happens to be higher in the pecking order than varchar in that regard. |
|
|
Henrik Svensson
Starting Member
25 Posts |
Posted - 2013-02-13 : 08:39:49
|
Ok. Thank you for your answers!With best regards,Henrik Svensson |
|
|
|
|
|