Calculation using CASE and apostrophes

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Calculation using CASE and apostrophes

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 understand

select '0.55000'+1.0,'0.55000'+1.00

Madhivanan

Failing 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources